CODEWITHSUNDEEP
postgresqldockerdockerfile
Vaibhav Kumar
Vaibhav Kumar

Reputation: 849

pg_restore in postgres docker container

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.

Upvotes: 35

Views: 48791

Answers (8)

Charlie Clark
Charlie Clark

Reputation: 19537

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.

Upvotes: 0

s33ds
s33ds

Reputation: 87

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.

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23837

(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

Upvotes: 2

Serhii Kushchenko
Serhii Kushchenko

Reputation: 948

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.

Upvotes: -1

xofred
xofred

Reputation: 184

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"

Upvotes: 9

jufx
jufx

Reputation: 154

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

Upvotes: 0

Zeus-Adenilton
Zeus-Adenilton

Reputation: 813

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

Upvotes: 56

Bernard
Bernard

Reputation: 17301

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"

Upvotes: 7

Related Questions