How can I restore Postgres DB with pg_basebackup in my case?

VladF

11/3/23, 11:31 AM

I have two DB servers and backup which is configured by pg_basebackup. You can see whole command below.

pg_basebackup -h $PGHOST -p $PGPORT -U $PGUSER -w -D $backup_dir/psql_base_backup_$(date +"%d-%m-%y") -l "$(date)" -F tar -z -R -P &>> $backup_dir/status.log

Below you can see my prod DB which I want to backup:

postgres=# \c sks-api
You are now connected to database "sks-api" as user "postgres".
sks-api=# \dt
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | clusters         | table | api
 public | migrations       | table | api
 public | nodepools        | table | api
 public | service_instance | table | api
 public | tasks            | table | api
 public | update_schedule  | table | api
 public | version_registry | table | api
(7 rows)

sks-api=# SHOW data_directory;
         data_directory
--------------------------------
 /var/lib/postgresql/13/cluster
(1 row)

Now I am trying to restore this DB for test purposes. I create the same two servers and doing below things:

systemctl stop postgresql
cd /home/user
tar -xzf base.tar.gz -C /var/lib/postgresql/13/cluster
tar -xzf pg_wal.tar.gz -C /var/lib/postgresql/13/cluster
systemctl start postgresql

But after that I see only two tables in sks-api db. What I did wrong?

sks-api=# \dt
                 List of relations
 Schema |    Name    | Type  |        Owner
--------+------------+-------+---------------------
 public | migrations | table | unknown (OID=16386)
 public | nodepools  | table | unknown (OID=16386)
(2 rows)

431

0 + 2

postgresql

Daniel Vérité

11/4/23, 12:38 PM

You're restoring over an existing cluster so the datadir will be a mix of old and new contents. Can you try wiping out `/var/lib/postgresql/13/cluster/*` before restoring and see if that makes a difference?

VladF

11/5/23, 9:40 PM

@DanielVérité I've tryed to move /var/lib/postgresql/13/cluster/ to /var/lib/postgresql/13/cluster_bak previously. After I've extracter tar to /var/lib/postgresql/13/cluster

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How can I restore Postgres DB with pg_basebackup in my case?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.