pg_restore in postgres docker container

Question

I am trying to restore database in PostgreSQL docker container using pg_restore from a shellscript that will be called from docker file. I'm getting following error "ERROR: canceling autovacuum task CONTEXT: automatic analyze of table 'tablename'".

DockerFile:

    FROM postgres:9.3
    ENV POSTGRES_USER postgres
    ENV POSTGRES_PASSWORD Abcd1234
    ENV POSTGRES_DB Clarion1
    COPY DB.backup /var/lib/postgresql/backup/DB.backup
    COPY initialize.sh /docker-entrypoint-initdb.d/initialize.sh

initialize.sh

    #!/bin/bash
    set -e
    set -x

    echo "******PostgreSQL initialisation******"
    pg_restore -C -d DB /var/lib/postgresql/backup/DB.backup

Log:

    server started
    CREATE DATABASE
    /docker-entrypoint.sh: running /docker-entrypoint-initdb.d/initialize.sh
    ++ echo '******PostgreSQL initialisation******'
    ++ pg_restore -C -d Clarion1 /var/lib/postgresql/backup/Clarion53.backup
    ******PostgreSQL initialisation******
    ERROR:  canceling autovacuum task

But if I try to restore DB from command prompt in host machine from same backup file , it is working fine.

I think you probably need to wait for the database to be fully initialized before you run restore. — dnephin
– dnephin, Commented Sep 1, 2016 at 16:20

Zeus-Adenilton · Accepted Answer · 2019-09-17 15:48:16Z

56

Here is a way to restore from a file located on the host machine:

docker exec -i container_name pg_restore -U postgres_user -v -d database_name < /dir_backup_outside_container/file_name.tar

answered Sep 17, 2019 at 15:48

Zeus-Adenilton

8137 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sajid Over a year ago

Cant't we pg_restore --host=db ..... Can't we access the container network from host machine?

sajid Over a year ago

What about "pg_restore --host=container_address .." Can't we access the container network from host machine? It seems ev1 just copied and pasted the same answer for the sake of upvote.

xofred · Accepted Answer · 2022-01-05 06:35:43Z

Combine the most voted and Heroku's guide, I came up with this:

docker exec -i mohe-bc_db_1 pg_restore --verbose --clean --no-acl --no-owner -U postgres -d mohe-bc_development < ~/Downloads/de8dc786-b133-4ae2-a040-dcf34f12c3de

mohe-bc_db_1: pg container name showed by docker ps NAMES column

postgres: pg username

mohe-bc_development: db name

~/Downloads/de8dc786-b133-4ae2-a040-dcf34f12c3de: file path of pg db dump

And it works:

pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT webhooks webhooks_pkey
pg_restore: dropping CONSTRAINT schema_migrations schema_migrations_pkey
pg_restore: dropping CONSTRAINT ar_internal_metadata ar_internal_metadata_pkey
pg_restore: dropping DEFAULT webhooks id
pg_restore: dropping SEQUENCE webhooks_id_seq
pg_restore: dropping TABLE webhooks
pg_restore: dropping TABLE schema_migrations
pg_restore: dropping TABLE ar_internal_metadata
pg_restore: creating TABLE "public.ar_internal_metadata"
pg_restore: creating TABLE "public.schema_migrations"
pg_restore: creating TABLE "public.webhooks"
pg_restore: creating SEQUENCE "public.webhooks_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.webhooks_id_seq"
pg_restore: creating DEFAULT "public.webhooks id"
pg_restore: processing data for table "public.ar_internal_metadata"
pg_restore: processing data for table "public.schema_migrations"
pg_restore: processing data for table "public.webhooks"
pg_restore: executing SEQUENCE SET webhooks_id_seq
pg_restore: creating CONSTRAINT "public.ar_internal_metadata ar_internal_metadata_pkey"
pg_restore: creating CONSTRAINT "public.schema_migrations schema_migrations_pkey"
pg_restore: creating CONSTRAINT "public.webhooks webhooks_pkey"

Bernard · Accepted Answer · 2016-09-01 15:10:23Z

7

I don't think the backup restore can be done during the initialization phase. Start your container and then upload the db.

docker run -d --name mydb mypgimage
docker exec mydb sh -c "pg_restore -C -d DB /var/lib/postgresql/backup/DB.backup"

answered Sep 1, 2016 at 15:10

Bernard

17.6k13 gold badges68 silver badges69 bronze badges

Comments

Cetin Basoz · Accepted Answer · 2022-07-17 16:56:44Z

4

(Adding this just for windows users, < is not supported by powerShell). Under powershell:

Get-Content C:\pathToDumpFolder\Mydump.sql | docker exec -i containername psql -U username -v -d dbname

answered Jul 17, 2022 at 16:56

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Comments

Charlie Clark · Accepted Answer · 2024-01-30 10:56:01Z

1

I've just come across this myself. Here's how I managed it without copying the data, assuming the database server is mapped to a port on the local host: you'll see this in the container overview: Postgres normally runs on port 5432 but this will be mapped to another port, if you have Postgres on a host. This is the case on my machine, Inspect the container to see which ip-address has been given for docker. On my machine it's 0.0.0.0, which is probably standard. You can test this with

psql -h 0.0.0.0 -p 6543 -U db_user db_name

You now have all the information to restore from the host:

pg_restore -U db_user -h 0.0.0.0 -p 6543 --dbname=db_name db.dump

Where db_name of the database and db_user its owner, and db.dump the name of the dump file, add path if required. Set flags as required.

answered Jan 30, 2024 at 10:56

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

1 Comment

oligofren Over a year ago

"which ip-address has been given for docker. " That's not the ip address. It is which ip-address the port has been bound to. 0.0.0.0 means you have exposed the port to the world, meaning someone from the outside can connect to it. You should only expose the port on localhost.

jufx · Accepted Answer · 2021-10-15 08:21:44Z

0

This one worked for me from a pg_dump -Fc 'pg_dump_Fc_file' custom (compressed) database dump:

docker exec -i container_name pg_restore -Fc -U admin_username -d database_name < pg_dump_Fc_file

answered Oct 15, 2021 at 8:21

jufx

1541 silver badge8 bronze badges

Comments

s33ds · Accepted Answer · 2023-11-22 18:34:10Z

0

You can use the docker entrypoint, in your dockerfile:

RUN echo "pg_restore -U postgres [FILE_LOCATION_HERE]" > /docker-entrypoint-initdb.d/init.sh

Working nicely for me.

edited Nov 22, 2023 at 18:34

answered Nov 21, 2023 at 21:44

s33ds

879 bronze badges

Comments

Serhii Kushchenko · Accepted Answer · 2022-01-27 10:54:56Z

-1

Another variant in case all the shorter ones don't work:

docker exec -i container_name pg_restore -U db_user --verbose --clean --no-acl --no-owner -h localhost -d db_name < db_backup_file

Also, pay attention to the --format option.

edited Jan 27, 2022 at 10:54

answered Jan 27, 2022 at 9:57

Serhii Kushchenko

98811 silver badges19 bronze badges

Collectives™ on Stack Overflow

pg_restore in postgres docker container

8 Answers 8

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related