Programming/PostgreSQL/Databases - Revision history

Brodriguez: Update section to allow database privileges

2025-04-04T06:52:59Z

Update section to allow database privileges

← Older revision		Revision as of 06:52, 4 April 2025
Line 21:		Line 21:
	=== Set Database Privileges ===		=== Set Database Privileges ===
	GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;		GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;

			On newer installations of Postgres, you may also need the following command for the user to properly create/modify tables:
			# First, load the desired database:
			\c <database_name>

			# Then run command:
			GRANT ALL ON SCHEMA public TO <user>;


	== Database Backups ==		== Database Backups ==

2023-03-03T08:02:52Z

Correct command

← Older revision		Revision as of 08:02, 3 March 2023
Line 24:		Line 24:
	== Database Backups ==		== Database Backups ==
	=== Export Database to file ===		=== Export Database to file ===
	pg_dump -U <user_name> -d <database_name> > <file_location>		pg_dump -Fc -U <user_name> -d <database_name> > <file_location>

	Optional Flags:		Optional Flags:

2023-01-26T23:44:20Z

Add query

← Older revision		Revision as of 23:44, 26 January 2023
Line 5:		Line 5:
	=== Show All Databases ===		=== Show All Databases ===
	\l		\l

			=== Show Currently Selected Database ===
			SELECT current_database();

	=== Create a New Database ===		=== Create a New Database ===

2021-09-10T01:18:15Z

Correct command

← Older revision		Revision as of 01:18, 10 September 2021
Line 21:		Line 21:
	== Database Backups ==		== Database Backups ==
	=== Export Database to file ===		=== Export Database to file ===
	pg_dump -U <user_name> -d <database_name> <file_location>		pg_dump -U <user_name> -d <database_name> > <file_location>

	Optional Flags:		Optional Flags:

2020-11-20T07:14:47Z

Add optional command flags

← Older revision		Revision as of 07:14, 20 November 2020
Line 22:		Line 22:
	=== Export Database to file ===		=== Export Database to file ===
	pg_dump -U <user_name> -d <database_name> <file_location>		pg_dump -U <user_name> -d <database_name> <file_location>

			Optional Flags:
			* {{ ic \|-h <host>}} - Allows specification of host, such as for connecting to a remote server.
			* {{ ic \|-p <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|5432}}.

	=== Import Database from File ===		=== Import Database from File ===
	pg_restore -U <user_name> -d <database_name> <file_location>		pg_restore -U <user_name> -d <database_name> <file_location>

			Optional Flags:
			* {{ ic \|-h <host>}} - Allows specification of host, such as for connecting to a remote server.
			* {{ ic \|-p <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|5432}}.

2020-11-20T06:44:13Z

Organize page

← Older revision		Revision as of 06:44, 20 November 2020
Line 2:		Line 2:

	== General Commands ==		== General Commands ==

			=== Show All Databases ===
			\l

	=== Create a New Database ===		=== Create a New Database ===
	CREATE DATABASE <database_name>;		CREATE DATABASE <database_name>;

			=== Load a Database ===
			\c <database_name>

	=== Delete a Database ===		=== Delete a Database ===
	{{ warn \| Note that this action cannot be undone. }}		{{ warn \| Note that this action cannot be undone. }}
	DROP DATABASE <database_name>;		DROP DATABASE <database_name>;

	~~=== Show All Databases ===~~
	\l

	~~=== Load a Database ===~~
	~~\c <database_name>~~

	=== Set Database Privileges ===		=== Set Database Privileges ===

2020-11-20T06:41:21Z

Add a warning

← Older revision		Revision as of 06:41, 20 November 2020
Line 2:		Line 2:

	== General Commands ==		== General Commands ==
	=== ~~Creating~~ a New Database ===		=== Create a New Database ===
	CREATE DATABASE <database_name>;		CREATE DATABASE <database_name>;

	=== ~~Deleting~~ a Database ===		=== Delete a Database ===
			{{ warn \| Note that this action cannot be undone. }}
	DROP DATABASE <database_name>;		DROP DATABASE <database_name>;

Line 11:		Line 12:
	\l		\l

	=== Load Database ===		=== Load a Database ===
	\c <database_name>		\c <database_name>

2020-11-20T04:41:57Z

Correct typo

← Older revision		Revision as of 04:41, 20 November 2020
Line 1:		Line 1:
	{{ note \| Unless otherwise specified, all commands from this ~~section~~ assume you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}		{{ note \| Unless otherwise specified, all commands from this page assume you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}

	== General Commands ==		== General Commands ==

2020-11-20T04:41:09Z

Clean up page a bit

← Older revision		Revision as of 04:41, 20 November 2020
Line 1:		Line 1:
	~~== Creating a New Database ==~~		{{ note \| Unless otherwise specified, all commands from this section assume you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}
	~~=== Via Linux Shell ===~~
	~~From~~ the PostgreSQL shell:
	~~CREATEDB <database_name>;~~

	=== ~~Via PostgreSQL Shell~~ ===		== General Commands ==
			=== Creating a New Database ===
	CREATE DATABASE <database_name>;		CREATE DATABASE <database_name>;

	== Deleting a Database ==		=== Deleting a Database ===
	DROP DATABASE <database_name>;		DROP DATABASE <database_name>;

	== Show All Databases ==		=== Show All Databases ===
	~~From the PostgreSQL shell:~~
	\l		\l

	== Load Database ==		=== Load Database ===
	\c <database_name>		\c <database_name>

	== ~~Give~~ Database Privileges ~~to User~~ ==		=== Set Database Privileges ===
	~~From the PostgreSQL shell:~~
	GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;		GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;

			== Database Backups ==
			=== Export Database to file ===
			pg_dump -U <user_name> -d <database_name> <file_location>

			=== Import Database from File ===
			pg_restore -U <user_name> -d <database_name> <file_location>

2020-11-03T00:28:16Z

← Older revision

Revision as of 00:28, 3 November 2020