1

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.

1

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

  1. 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
  1. 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.

  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.