How to do schema only backup and restore in PostgreSQL? | PostgreSQL Tutorials and Solutions

In this article, we will see how to do schema only backup and restore in PostgreSQL.
Schema only backup contains only objects definition like table definition.

To take a schema level backup in PostgreSQL database and restore on the another database, we use pg_dump utility with the option –schema-only or -s. If we gave –schema-only means, it dumps only the object definitions (schema), not data.

Syntax to take schema only backup in PostgreSQL

pg_dump -d database_name -U username -h hostname --schema-only > backuppath/filename.dump

Example:

We are going to take schema only backup of my database named ‘r2schools’ and user name is postgres and hostname is localhost.So, not providing hostname in the following example.

pg_dump -d r2schools -U postgres --schema-only > /var/lib/postgresql/backups/r2schoolsschemaonlybackup.dump

Then, provide the password of user postgres.

Syntax to restore schema only backup in PostgreSQL

Before restoring, we must have the database already exists. Otherwise we have to create it.

To create database use the below syntax and provide the database:

psql -c "create database testr2schools;"

We are going to restore schema only backup to the database named ‘testr2schools’ and user name is postgres and hostname is localhost. So, not providing hostname in the following example.

psql -d testr2schools -U postgres

Then provide the password of user postgres.

Lets login into PostgreSQL server and verify schema objects are restored successfully or not.

Related Posts: