PostgreSQL

Backup & Restore

Backup

Backup one database

pg_dump DATABASE_NAME > /PATH/TO/DUMPFILE.sql

Backup entire cluster

pg_dumpall > /PATH/TO/DUMPFILE_ALL.sql

Restore

Restore one database

psql DATABASE_NAME < /PATH/TO/DUMPFILE.sql

Restore entire cluster

psql -f /PATH/TO/DUMPFILE_ALL.sql postgres

Management

List Databases

psql --list \
    --host HOSTNAME \
    --port PORT \
    --username USER \
    --password

In the psql Shell

\l  # List databases
\du # List users
\dt # List tables in connected database
\dn # List of schemas

\c DATABSE  # To connect to database
\d TABLE    # To get information about specific table

\q  # Exit the Shell

Set Permission

# Alter Database Owner
ALTER DATABASE database OWNER TO username;

# Reassign (Change) Table Owner
REASSIGN OWNED BY avnadmin to mautrix

# Add Gropu to user
GRANT group_role TO role

# Remove Group from User
REVOKE group_role FROM role

# Update user password
ALTER ROLE username WITH PASSWORD 'ExamplePassword';

# Update global permission
ALTER ROLE username WITH

# Add all permission on database to user
GRANT ALL ON DATABASE database TO username;
GRANT ALL PRIVILEGES ON DATABASE database TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username;