mardi 27 janvier 2015

pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists


I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined below) but still got these errors:



pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;

Steps to reproduce:



  1. Install a fresh, vanilla Ubuntu 14.04 distro (I'm using Vagrant with this Vagrant box).

  2. Install PostgreSQL 9.3, configure to allow local connections as PostgreSQL user "postgres" from any Linux user.


  3. Create a test database. I'm just doing:



    vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres postgres
    psql (9.3.5)
    Type "help" for help.

    postgres=# create database mydb;
    CREATE DATABASE
    postgres=# \q
    vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres mydb
    psql (9.3.5)
    Type "help" for help.

    mydb=# create table data(entry bigint);
    CREATE TABLE
    mydb=# insert into data values(1);
    INSERT 0 1
    mydb=# insert into data values(2);
    INSERT 0 1
    mydb=# insert into data values(3);
    INSERT 0 1
    mydb=# \q


  4. Create a backup of the database like so:



    PGPASSWORD="postgres" pg_dump --dbname=mydb --username=postgres --format=custom > pg_backup.dump


  5. Delete some rows out of the data table in mydb so we will be able to tell if we restored the data successfully.




  6. Restore the database with:



    PGPASSWORD="postgres" pg_restore --clean --create --dbname=postgres --username=postgres pg_backup.dump


The data is restored, but the pg_restore command in step 6 exits with status 1 and shows the following output:



pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;



WARNING: errors ignored on restore: 1

I cannot just ignore this because I am running this command programmatically and need to use the exit status to determine if the restore failed or not. Initially, I wondered if this problem was because I put my database in public (the default schema). I reasoned that public would be created as a result of the --create option by pg_restore before the data was restored (which could conceivably try to create that schema as well since that is where my table is), but when I tried the above steps with my table in a different schema, the results were the same and the error messages were identical.


Am I doing something wrong? Why am I seeing this error?





Aucun commentaire:

Enregistrer un commentaire