Skip to content

Database Operations

Note

Please update the parameters provided inside the brackets <> in the code before executing the code.

Role and Access

Creating a role

CREATE ROLE <role-name> LOGIN PASSWORD '<password>';

Update password for a the role

ALTER ROLE <role-name> WITH PASSWORD '<password>';

Provide grants to schema for the role or user

GRANT USAGE ON SCHEMA public TO <role-or-user-name>;
GRANT ALL ON ALL TABLES IN SCHEMA public TO <role-or-user-name>;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO <role-or-user-name>;

Providing grants to a database

GRANT CONNECT ON DATABASE <db-name> TO <role-or-user-name>;

DB Management

Creating DB

createdb -p ${postgres-db-port} -U ${postgres-db-user} ${db-name}

OR

/usr/pgsql-<version>/bin/createdb -p ${postgres-db-port} -U ${postgres-db-user} ${db-name}

Creating DB Backup

Note

This creates a tar backup file.

pg_dump -C -U ${postgres-db-user} -p ${postgres-db-port} -F t ${db-name} > ${db-file>}.tar

OR

/usr/pgsql-<version>/bin/pg_dump  -C -U ${postgres-db-user} -p ${postgres-db-port} -F t ${db-name} > ${db-file>}.tar

Restoring a DB using backup file

pg_restore  -d ${db-name} ${db-file>}.tar  -c -U ${role-or-user-name} -p ${postgres-db-port}

OR

/usr/pgsql-<version>/bin/pg_restore  -d ${db-name} ${db-file>}.tar  -c -U ${role-or-user-name} -p ${postgres-db-port}

Miscellaneous

Running SQL queries from bash

psql -U ${postgres-db-user} -p ${postgres-db-port}  -c '<query>'

psql -U ${postgres-db-user} -p ${postgres-db-port} -c 'GRANT CONNECT ON DATABASE ${my-db} TO ${my-db-user}'
Back to top