<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=2826169&amp;fmt=gif">
Start  trial

    Start trial

      When restoring database objects, an error will occur if the target objects already exist at the destination. By deleting the database objects at the restore destination at the same time as the restore, you can save the trouble and avoid such errors.

      There are two ways to do this.

      • At the time of backup, create a backup file that contains a SQL command that deletes database objects.

        Back up using the pg_dump command with the -c or --clean option, and restore it with the psql command.

        - or -

      • Delete the database objects and then execute the restore.

        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.

      Execution example

      In the example below, we back up data using the pg_dump command and then restore the backed up objects.

      1. Back up the target database (mydb) using the pg_dump command
        $ pg_dump -c --if-exists mydb > mydb_dump.sql
      2. Use psql command to revert to the last backup
        $ psql mydb < mydb_dump.sql

      Key points

      • If restore fails with an error indicating that the objects cannot be deleted, check if it is safe to delete the target objects. Also, when restore is complete, check if the restored objects do not have any problems.
      • If you do not specify the --if-exists option with the -c option, the following error may be output.

        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
        1 This is output to the server log - it is not displayed on the standard output when the log collection feature is enabled. If you specify the --if-exists option with the -c option for backup, the DROP statement with the IF EXISTS clause will be written, so you can avoid the above error. The --if-exists option has been supported since PostgreSQL 9.4
      For more information on how to extract a PostgreSQL database into a script file or other archive file, refer to the PostgreSQL documentation, section pg_dump.

      Related PostgreSQL Insider articles

      PostgreSQL backup and recovery
      This article introduces the basic concepts and types of backup and recovery in PostgreSQL, as well as examples of how to choose a method for a desired outcome.
      Logical backup and recovery
      This article covers the various methods of logical backup and recovery, and which method is useful in what kind of use cases.
      logcial-backup-and-recoery
      Restoring a database to another database cluster
      This article provides the steps and key points to keep in mind when restoring a database to another database cluster.
      restore-to-another-cluster

      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.

      Brochure

      See how FUJITSU Enterprise Postgres enhances PostgreSQL providing 5 key enterprise capabilities for your mission-critical applications.

      1st  page brochure FUJITSU Enterprise Postgeres - Fujitsu's enhanced open source PostgreSQL
      View brochure

      Read our latest blogs

      Read our most recent articles regarding all aspects of PostgreSQL and Fujitsu Enterprise Postgres.