Postgresql cannot restore partial pg_dump “relation ”public.the_table_seq“ does not exist”

dockerpg-dumppostgresql-9.6

In my database I dumped some tables via the following psql command:

pg_dump -t my_table1 -t mytable2 > my_dump.sql

And I try to import it into a postgresql docker image as a standalone database using docker-compose.yml:

version: '3.1'
services:
    postgresql:
        image: postgres:9.6
        volumes:
        - './my_dump.sql:/docker-entrypoint-initdb.d/my_dump.sql'
        environment:
        POSTGRES_USER: $MY_DB_USER
        POSTGRES_PASSWORD: $MY_DB_PASSWD
        POSTGRES_DB: $MY_DB_NAME

But I get the following error:

postgresql_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/my_dump.sql
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  |  set_config 
postgresql_1  | ------------
postgresql_1  |  
postgresql_1  | (1 row)
postgresql_1  | 
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | ERROR:  relation "public.my_table_seq" does not exist
postgresql_1  | STATEMENT:  CREATE TABLE public.my_table (
postgresql_1  |         table_id integer DEFAULT nextval('public.my_table'::regclass) NOT NULL,
postgresql_1  |         name character varying(50) NOT NULL,
postgresql_1  |         mode character varying(50) NOT NULL,
postgresql_1  |         workers integer DEFAULT 1
postgresql_1  |     );
postgresql_1  | psql:/docker-entrypoint-initdb.d/cue.sql:32: ERROR:  relation "public.my_table_seq" does not exist
cue_docker_postgresql_1 exited with code 3

Meaning that for some reason I fail to dump the sequence as well via pg_dump. So I want to know how I can pg_dump specific tables alongside with their respective sequences used.

What try to do with that is to split an existing database schema into seperate databases due to its sheer size and also for scalability reasons as well.

Best Answer

I had also a similar case and I solved it via doing theese steps:

First of all I made an initial psql_dump with the tables I wanted to dump:

 pg_dump -t my_table1 -t mytable2 > my_dump.sql

Then I listed any sequence existing by filtering them, from the existing dump I made. In case of larger dumps use tools offered in bash or in powershell in order to filter them out. In other words you need to search any lines having DEFAULT nextval( and keep only the sequence name.
Once found the all the sequences do an another pg_dump as the one follows:

     pg_dump -O -x -t my_table_seq -t another_table_seq -t my_table1 -t mytable2 > my_dump.sql

In the command above the my_table_seq and the another_table_seq are sequences used. Also with the -x and -O parameters I ewxclude dumping any permission/role related sql so in the new database will contain only the tables and their respective data.

Related Solutions

Postgresql – pg_dump does not honor -n

When dumping a schema with the mentioned command, as postgres user:

pg_dump -f file.sql -F plain --inserts -n schemaname database

it produces a file whose basic structure looks like:

-- various SET commands
CREATE SCHEMA schemaname;

ALTER SCHEMA schemaname OWNER TO ownername;

SET search_path = schemaname, pg_catalog;

CREATE TABLE tablename(...) ...

ALTER TABLE schemaname.tablename OWNER TO ownername;

COPY tablename (columns...) FROM stdin;
data...
\.

The fact that search_path is set to the newly created schema implies that the CREATE TABLE and COPY statements that follow will happen in that schema and not in the public schema.

Or SET search_path would have to fail, which shouldn't happen even if the creation of the schema just above in the script failed. If schemaname does not exist, the next schema tried would be pg_catalog and CREATE TABLE would refuse to write into it, and after that there's no next schema in the search path, and in particular not public.

It's hard to imagine what mishappening could cause the above script to end up creating and populating anything in the public schema.

Postgresql – pg_restore ERROR: “Relation does not exist” and creating new database

When --create and -d are used together, the argument to -d is not the name of the database to create, it's the name of an existing database to connect to run the CREATE DATABASE statement, because it's impossible to create a database if you're not already connect to another database.

This is documented as:

When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

This is why pg_restore errors out with database temp does not exist

You should add a createdb step in your restore procedure since you want to create a specific database name anyway, not one that comes from the backup.

For the second problem, it's a completely different question, hard to guess why it happens without complete error messages.

Also note that -p is not followed by a password but by a port number.

Best Answer

Related Solutions

Postgresql – pg_dump does not honor -n

Postgresql – pg_restore ERROR: “Relation does not exist” and creating new database

Related Question