There are two ways to do this.
Back up using the pg_dump command with the -c or --clean option, and restore it with the psql command.
- or -
Restore using the pg_restore command with the -c or --clean option.
In this article, we explain the first moethod.
In the output result of the pg_dump command, the SQL command required to revert the database to the last backup point is written. If you specify the command with the -c or --clean option, the command to delete database objects will be written before the command that creates database objects. Therefore, during restore, database objects are cleared before rebuilding the database.
You can also specify the --if-exists option to avoid an error occuring if objects exist in the backup data but not in the destination database.
In the example below, we back up data using the pg_dump command and then restore the backed up objects.
$ pg_dump -c --if-exists mydb > mydb_dump.sql
$ psql mydb < mydb_dump.sql
This is because the file read during restore contains a DROP statement for a nonexistent object. The error in this case is harmless.
If the table is deleted after backup using the pg_dump command with the -c option, the following error will be output when restoring.
ERROR: Table "tableName" does not exist 1
Statement: DROP TABLE tableName; 1
ERROR: Table "tableName" does not exist
Fujitsu Enterprise Postgres is designed for developers, data architects, and DBAs seeking the efficiency of a leading open source based database technology with the enhanced security and performance features plus support required in a serious business environment.
Read our most recent articles regarding all aspects of PostgreSQL and Fujitsu Enterprise Postgres.