Programming/PostgreSQL/Databases: Difference between revisions
< Programming | PostgreSQL
Jump to navigation
Jump to search
Brodriguez (talk | contribs) m (Brodriguez moved page PostgreSQL/Databases to Programming/PostgreSQL/Databases) |
Brodriguez (talk | contribs) (Update section to allow database privileges) |
||
| (8 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
{{ note | Unless otherwise specified, all commands from this page assume you're in the [[PostgreSQL#Basics | PostgreSQL shell]]. }} | |||
=== | == General Commands == | ||
=== Show All Databases === | |||
\l | |||
=== Show Currently Selected Database === | |||
SELECT current_database(); | |||
=== Create a New Database === | |||
CREATE DATABASE <database_name>; | CREATE DATABASE <database_name>; | ||
== | === Load a Database === | ||
\c <database_name> | |||
=== Delete a Database === | |||
{{ warn | Note that this action cannot be undone. }} | |||
DROP DATABASE <database_name>; | DROP DATABASE <database_name>; | ||
== | === Set Database Privileges === | ||
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> | \c <database_name> | ||
# Then run command: | |||
GRANT ALL ON SCHEMA public TO <user>; | |||
== Database Backups == | |||
=== Export Database to file === | |||
pg_dump -Fc -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 === | ||
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}}. | |||
Latest revision as of 06:52, 4 April 2025
Note: Unless otherwise specified, all commands from this page assume you're in the PostgreSQL shell.
General Commands
Show All Databases
\l
Show Currently Selected Database
SELECT current_database();
Create a New Database
CREATE DATABASE <database_name>;
Load a Database
\c <database_name>
Delete a Database
Warn: Note that this action cannot be undone.
DROP DATABASE <database_name>;
Set Database Privileges
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
Export Database to file
pg_dump -Fc -U <user_name> -d <database_name> > <file_location>
Optional Flags:
-h <host>- Allows specification of host, such as for connecting to a remote server.-p <port>- Allows specification of port, such as for non-standard installations that don't use the default port5432.
Import Database from File
pg_restore -U <user_name> -d <database_name> <file_location>
Optional Flags:
-h <host>- Allows specification of host, such as for connecting to a remote server.-p <port>- Allows specification of port, such as for non-standard installations that don't use the default port5432.