Unable to load a database from a restore that was done in a new volume

Question

I'm having trouble restoring a postgres database volume with docker-compose.

Here are the steps:

1. Backuping the volume:

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar /var/lib/postgresql/data

2. Creating a volume (which already has the correct name to be used with docker-compose after):

$ docker volume create folder_test-db

3. Restoring the database in that volume:

$ docker run --rm -v folder_test-db:/recover \
    -v /mnt/data/dbbackups/:/backup ubuntu bash \
    -c "cd /recover && tar xvf /backup/backup.tar"

Then,

$ cd /path/to/restore/test/folder
$ docker-compose up

is giving:

Creating network "folder_default" with the default driver
Creating folder_test-db_1 ... done
Attaching to folder_test-db_1
test-db_1  | initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
test-db_1  | If you want to create a new database system, either remove or empty
test-db_1  | the directory "/var/lib/postgresql/data" or run initdb
test-db_1  | with an argument other than "/var/lib/postgresql/data".
test-db_1  | The files belonging to this database system will be owned by user "postgres".
test-db_1  | This user must also own the server process.
test-db_1  | 
test-db_1  | The database cluster will be initialized with locale "en_US.utf8".
test-db_1  | The default database encoding has accordingly been set to "UTF8".
test-db_1  | The default text search configuration will be set to "english".
test-db_1  | 
test-db_1  | Data page checksums are disabled.
test-db_1  | 
folder_test-db_1 exited with code 1

Here's the compose file content:

version: '3.8'

volumes:
  test-db: {}

services:
  test-db:
    image: postgres:12.3
    environment:
      POSTGRES_DB: myapp-db
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    volumes:
      - test-db:/var/lib/postgresql/data
    ports:
      - 5432:5432

The postgres image version is the exact same than the one of the original container I have dumped the volume from.

Edit

I found a trick, but it didn't work either:

Instead of trying to spin up the new compose file after having restoring the database to a new volume, I just did the opposite;

spin up the compose file; this will init a new postgres database with the user given in the compose file,
stop the container,
do the restore, now on a volume which already exists because it was initialized by the first step,
restart the container.

The database got created, the role is correct;

user=# \conninfo
You are connected to database "myapp-db" as user "user" via socket in "/var/run/postgresql" at port "5432".

But heck, where are the data ?! :

user=# \dt
Did not find any relations.

Question

How could I get my database back with docker-compose?
Because in my original app, when the db already exists, docker-compose skips the init step and goes on with the existing content. I wish I could have the same behaviour after a restore, especially when it was done with the help of the docker daemon itself!

Edit

This is the part of the compose file (located in rootdir) that build the original volume of my app's databse, no fancy stuff in there I guess:

version: "3.5"

services:
  myapp-db:
    image: postgres:12.3
    environment:
      POSTGRES_DB: myapp-db
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    volumes:
      - myapp-db-data:/var/lib/postgresql/data
    networks:
      - myapp
    ports:
      - 5433:5432

volumes:
  myapp-db-data:

Here is the content of /var/lib/postgresql/data of that original container:

# ls -larth /var/lib/postgresql/data
total 132K
-rw-------  1 postgres postgres    3 Feb  6 15:13 PG_VERSION
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_twophase
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_tblspc
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_snapshots
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_serial
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_replslot
drwx------  4 postgres postgres 4.0K Feb  6 15:13 pg_multixact
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_dynshmem
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_commit_ts
-rw-------  1 postgres postgres  26K Feb  6 15:13 postgresql.conf
-rw-------  1 postgres postgres   88 Feb  6 15:13 postgresql.auto.conf
-rw-------  1 postgres postgres 1.6K Feb  6 15:13 pg_ident.conf
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_xact
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_subtrans
-rw-------  1 postgres postgres 4.5K Feb  6 15:13 pg_hba.conf
drwx------  6 postgres postgres 4.0K Feb  6 15:13 base
drwx------  3 postgres postgres 4.0K Feb  6 17:32 pg_wal
drwx------  4 postgres postgres 4.0K Feb  6 22:44 pg_logical
drwx------  2 postgres postgres 4.0K Feb  7 08:32 pg_notify
-rw-------  1 postgres postgres   36 Feb  7 08:32 postmaster.opts
-rw-------  1 postgres postgres   94 Feb  7 08:32 postmaster.pid
drwx------  2 postgres postgres 4.0K Feb  7 08:32 pg_stat
drwx------  2 postgres postgres 4.0K Feb  7 08:32 global
drwx------  2 postgres postgres 4.0K Feb  7 08:33 pg_stat_tmp

and of the restored volume:

# ls -larth /var/lib/postgresql/data
total 136K
-rw-------  1 postgres postgres    3 Feb  7 00:10 PG_VERSION
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_twophase
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_tblspc
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_snapshots
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_serial
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_replslot
drwx------  4 postgres postgres 4.0K Feb  7 00:10 pg_multixact
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_dynshmem
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_commit_ts
-rw-------  1 postgres postgres  26K Feb  7 00:10 postgresql.conf
-rw-------  1 postgres postgres   88 Feb  7 00:10 postgresql.auto.conf
-rw-------  1 postgres postgres 1.6K Feb  7 00:10 pg_ident.conf
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_xact
drwx------  3 postgres postgres 4.0K Feb  7 00:10 pg_wal
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_subtrans
-rw-------  1 postgres postgres 4.5K Feb  7 00:10 pg_hba.conf
drwx------  6 postgres postgres 4.0K Feb  7 00:10 base
drwxr-xr-x  3 postgres root     4.0K Feb  7 00:10 var
-rw-------  1 postgres postgres   36 Feb  7 00:14 postmaster.opts
drwx------  2 postgres postgres 4.0K Feb  7 00:14 pg_notify
-rw-------  1 postgres postgres   94 Feb  7 00:14 postmaster.pid
drwx------  2 postgres postgres 4.0K Feb  7 00:14 pg_stat
drwx------  2 postgres postgres 4.0K Feb  7 00:15 global
drwx------  4 postgres postgres 4.0K Feb  7 00:19 pg_logical
drwx------  2 postgres postgres 4.0K Feb  7 08:34 pg_stat_tmp

Everything is hosted on a Ubuntu 18.04 server.

swiss_knight · Answer 1 · 2021-02-07T09:27:36.283

TL;DR;

RTFM I have to change the way I compress the initial volume because doing it the way I did, actually stores the full absolute path of each single file that get compressed. see man tar and check for the -C (capital C) option.

So before (bad):

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar /var/lib/postgresql/data

After (good):

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar -C /var/lib/postgresql/data .

Details

Okay, by exploring more the newly created volume that hosts the restored database, I can see that I have, inside the /var/lib/postgres/data/ folder, this line that tickled my eyes:

drwxr-xr-x  3 postgres root     4.0K Feb  7 00:10 var

When I get in there, there was a whole copy of /var/lib/postgres/data/ from the original database! E.g. /var/lib/postgres/data/var/lib/postgres/data/<pgstuff>. There is was! So, something went wrong. I checked the extraction part from the host machine; no problem. I checked the compression part... and the error was there actually:

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar /var/lib/postgresql/data

indeed, tar cvf /backup/backup.tar /var/lib/postgresql/ do take into account the absolute paths of the files it has been asked to compress!

Hence, back to the basics #rtfm:

$ man tar
...
       -C, --directory=DIR
              Change to DIR before performing any operations.
              This option is order-sensitive, i.e. it affects all options that follow.

Clear enough for me, I changed the previous backuping command to:

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar -C /var/lib/postgresql/data .

Please, don't forget that tiny little dot at the end as it actually says to compress the directory tar just 'cd-ed' into!

From now, the restore is unchanged, and I can no more see that var folder in /var/lib/postgres/data/ inside the new volume!

And when I re-up my compose file, now I end up with was I waiting for:

$ cd /path/to/restore/test/folder
$ docker-compose up

Starting folder_test-db_1 ... done
Attaching to folder_test-db_1
test-db_1  | 
test-db_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
test-db_1  | 
test-db_1  | 2021-02-07 08:55:30.849 UTC [1] LOG:  starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
test-db_1  | 2021-02-07 08:55:30.849 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
test-db_1  | 2021-02-07 08:55:30.850 UTC [1] LOG:  listening on IPv6 address "::", port 5432
test-db_1  | 2021-02-07 08:55:30.858 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
test-db_1  | 2021-02-07 08:55:30.888 UTC [26] LOG:  database system was interrupted; last known up at 2021-02-07 08:37:37 UTC
test-db_1  | 2021-02-07 08:55:33.044 UTC [26] LOG:  database system was not properly shut down; automatic recovery in progress
test-db_1  | 2021-02-07 08:55:33.051 UTC [26] LOG:  redo starts at 0/1EA5948
test-db_1  | 2021-02-07 08:55:33.051 UTC [26] LOG:  invalid record length at 0/1EA5A30: wanted 24, got 0
test-db_1  | 2021-02-07 08:55:33.051 UTC [26] LOG:  redo done at 0/1EA59F8
test-db_1  | 2021-02-07 08:55:33.096 UTC [1] LOG:  database system is ready to accept connections

And inside that container, within psql -U user -d myapp-db:

# \conninfo
You are connected to database "myapp-db" as user "user" via socket in "/var/run/postgresql" at port "5432".

# \dt
                                List of relations
 Schema |                          Name                           | Type  | Owner 
--------+---------------------------------------------------------+-------+-------
 public | attachments_attachment                                  | table | user
 public | auth_group                                              | table | user
 public | auth_group_permissions                                  | table | user
 public | auth_permission                                         | table | user
....

And both previous ways of restoring are working very well.
You may prefer one or the other.

score 0 · Answer 2 · answered Feb 07 '21 at 08:10

0

Did you use a docker volume plugin to create the volume? If there are any files or folders in there like lost+found it will probably fail to initialize. If there are files that you want to keep in the volume (or have no control over) you could adjust the PGDATA environment variable to point to a sub-directory in there like

-e PGDATA=/var/lib/postgresql/data/db-files/

...
volumeMounts:
- mountPath: /var/lib/postgresql/data
  name: test-db-volume
  subPath: postgres
...

Can also refer to this more understanding

answered Feb 07 '21 at 08:10

Ashok

3,190
15
31

No, there is no fancy stuff in there, I've happened to the original question some more details on the questions you may have had. It actually put me on the right track, I explained in details what was the error and how exactly I solved it in the answer I posted nearby, for posterity. – swiss_knight Feb 07 '21 at 09:23

Unable to load a database from a restore that was done in a new volume

Edit

Question

Edit

2 Answers2

TL;DR;

Details