Purpose of this guide is to provide a step-by-step guide for setting up PostgreSQL and manage databases.
Install PostgreSQL Client
1
sudo apt-get install -y postgresql-client
Login to Database:
1
psql -U username -h hostname -p 5432
- Show users:
1
2
3
\du
# With All column
\du+
- Reset password:
1
ALTER USER <username> WITH PASSWORD 'new_password';
- Add permission:
1
2
3
ALTER ROLE <rolename|username> <permission>;
# Example:
ALTER ROLE username createdb;
- Delete permission from role:
1
2
3
AlTER ROLE <rolename|username> no<permission>;
# Example:
ALTER ROLE username noCreatedb;
- Create extension:
1
2
3
CREATE EXTENSION <extension_name>;
# Example:
CREATE EXTENSION "uuid-ossp";
- Create Role with admin access:
1
CREATE ROLE <rolename> WITH createdb createrole login password '<password>';
- Create standard user:
1
2
3
CREATE DATABASE <dbname>;
CREATE USER <username> WITH createdb createrole login password '<password>';
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username>;
- Change database owner:
1
2
\c <dbname>
REASSIGN OWNED BY <old-role> TO <new-role>;
- Dump Database:
1
2
3
4
5
6
7
# In Binary Format
pg_dump -Fc -h localhost -p 5432 -U username -d dbname > dbname.dump
pg_dump -Fc -v -h localhost -p 5432 -U username -d dbname -f dbname.dump
# Without owner Role
pg_dump -Fc -O -h localhost -p 5432 -U username -d dbname > dbname.dump
# In SQL Format
pg_dump -h localhost -p 5432 -U username dbname > dbname.sql
- Restore Database:
1
2
3
4
# In Binary Format
pg_restore -U username -d dbname -1 dbname.dump
# In SQL Format
psql -h localhost -p 5432 -U username -d dbname < dbname.sql
- Postgresql Permission Name and Description
| Role | Description |
|---|---|
| CreateDB | NoCreateDB | Will allow/deny user to create database. |
| CreateRole | NoCreateRole | Allow the role to create or change roles. |
| Inherit | NoInherit | Determine if the role to inherit privileges of roles of which it is a member. |
| Login | NoLogin | Allow the role to log in. |
| Replication | NoReplication | Determine if the role is a replication roles. |
| ByPassRLS | NoByPassRLS | Determine if the role to by pass a row-level security (RLS) policy. |
| CONNECTION LIMIT | Specify the number of concurrent connection a role can made, -1 means unlimited. |
| PASSWORD ‘password’ | PASSWORD NULL | Change the role’s password. |
| VALID UNTIL ‘timestamp’ | Set the date and time after which the role’s password is no long valid. Ex. 2050-01-01 |
PG_DUMP & PG_RESTORE Flags:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
General options:
-f, --file=FILENAME output file or directory name
-F, --format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
-v, --verbose verbose mode
-Z, --compress=0-9 compression level for compressed formats
Options controlling the output content:
-b, --blobs include large objects in dump
-O, --no-owner skip restoration of object ownership in
plain-text format
-x, --no-privileges do not dump privileges (grant/revoke)
Connection options:
-d, --dbname=DBNAME database to dump
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before dump