Privileges

Grant All | Object Affected: Tables of a Schema

GRANT ALL ON ALL TABLES IN SCHEMA [schema_name] to [db_user];

Grant All | Object Affected: Sequence of a Schema

GRANT ALL ON ALL SEQUENCES IN SCHEMA [schema_name] to [db_user];

Grant all privileges on a schema

GRANT ALL PRIVILEGES ON SCHEMA [schema_name] to [db_user];

---

Tables

Change Table Ownership

ALTER table [schema_name.table_name] owner to [db_user];

---

Schema

Drop a schema

psql [db_name]
db_name# DROP SCHEMA [schema_name];

Backup a schema

pg_dump --schema=[schema_name] [database_name] > [/path/file.sql]

Restore a schema

psql [database_name] -f [/path/file.sql]

---

Database

Dumping a database

pg_dump dbname > outfile

Restoring a database

Specific Scenario: Database should be erased or cleaned up before restoring the data dumped from a different server.

It is safer to rename the existing database and create a new one:

pqsl postgres
postgres=# alter database churva_db_v1 rename to churva_db_v2;
postgres=# create database churva_db_v1;
postgres=# \q

Restore the data dump to the newly created database:

new_db_name < /tmp/datadumpfile

Reference link: PostgreSQL Chapter 23. Backup and Restore