My requirement is to backup the data in PostgreSQL container, and considering the time-consuming issue, I plan to use WAL for incremental backup.
I don't want to distinguish between primary and backup servers, I only have one server, and I want to move the entire database to another location when needed, such as when I need to change the server address, etc.
The modifications made to the postgresql.conf data are as follows:
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
And two scripts have been written outside, the first is the basebackup script as follows:
#!/bin/bash
CONTAINER_NAME="timescaledb"
DB_USER="postgres"
temp_folder="/tmp/pgdata/backup"
BackupFolder="/home/user/backup"
timestamp=$(date +%Y%m%d)
docker exec $CONTAINER_NAME pg_basebackup -U postgres --checkpoint=fast -Ft -z -P -D $temp_folder
docker cp $CONTAINER_NAME:$temp_folder $BackupFolder/postgres_backup_$timestamp/
docker exec $CONTAINER_NAME rm -rf $temp_folder
The second script is the restore script as follows:
#!/bin/bash
CONTAINER_NAME="timescaledb"
temp_folder="/tmp/pgdata/backup"
timestamp=$(date +"%Y-%m-%d %H:%M:%S")
postgres_backup_path="/home/user/TimeScaleDB/timescaledb_data"
folder_name="/home/user/backup/postgres_backup_20230502"
docker exec $CONTAINER_NAME chmod -R 777 /mnt
docker stop $CONTAINER_NAME
sudo chmod -R 777 ${postgres_backup_path}
rm -rf ${postgres_backup_path}/*
mkdir -p ${postgres_backup_path}
tar -xf ${folder_name}/base.tar.gz -C ${postgres_backup_path}/
echo restore_command = 'cp /mnt/server/archivedir/%f %p' >> ${postgres_backup_path}/postgresql.auto.conf
echo recovery_target_time = '${timestamp}' >> ${postgres_backup_path}/postgresql.auto.conf
touch $postgres_backup_path/standby.signal
docker start $CONTAINER_NAME
until docker exec $CONTAINER_NAME pg_isready; do
sleep 1
done
docker exec $CONTAINER_NAME psql -U postgres -c "select pg_wal_replay_resume();"
I don't need to specify the time point to recover to, I just need to recover all records. However, I still cannot succeed, and I would like to ask what the correct way to do this.
Under this restore script, the problem I encountered is that it always tells me that a certain file does not exist, but I should not have this file in my folder, and I don't know why it is asking for this file to be restored.
The files in /mnt/server/archivedir are shown in the picture. enter image description here
And the container's logs after I used the restore script as follows:
2023-05-02 08:33:32.285 UTC [1] LOG: received fast shutdown request
2023-05-02 08:33:32.287 UTC [1] LOG: aborting any active transactions
2023-05-02 08:33:32.288 UTC [51] LOG: shutting down
2023-05-02 08:33:32.323 UTC [1] LOG: database system is shut down
2023-05-02 08:33:33.284 UTC [1] LOG: starting PostgreSQL 12.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
2023-05-02 08:33:33.284 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-05-02 08:33:33.284 UTC [1] LOG: listening on IPv6 address "::", port 5432
2023-05-02 08:33:33.289 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-05-02 08:33:33.336 UTC [21] LOG: database system was interrupted; last known up at 2023-05-02 05:56:02 UTC
2023-05-02 08:33:33.454 UTC [28] FATAL: the database system is starting up
2023-05-02 08:33:33.704 UTC [21] LOG: restored log file "00000002.history" from archive
2023-05-02 08:33:33.704 UTC [21] LOG: restored log file "00000003.history" from archive
2023-05-02 08:33:33.705 UTC [21] LOG: restored log file "00000004.history" from archive
2023-05-02 08:33:33.706 UTC [21] LOG: restored log file "00000005.history" from archive
2023-05-02 08:33:33.706 UTC [21] LOG: restored log file "00000006.history" from archive
2023-05-02 08:33:33.707 UTC [21] LOG: restored log file "00000007.history" from archive
2023-05-02 08:33:33.708 UTC [21] LOG: restored log file "00000008.history" from archive
2023-05-02 08:33:33.709 UTC [21] LOG: restored log file "00000009.history" from archive
2023-05-02 08:33:33.710 UTC [21] LOG: restored log file "0000000A.history" from archive
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
2023-05-02 08:33:33.711 UTC [21] LOG: entering standby mode
2023-05-02 08:33:33.712 UTC [21] LOG: restored log file "0000000A.history" from archive
2023-05-02 08:33:33.723 UTC [21] LOG: restored log file "0000000A0000000000000003" from archive
2023-05-02 08:33:33.737 UTC [21] LOG: restored log file "00000002.history" from archive
2023-05-02 08:33:33.740 UTC [21] LOG: restored log file "00000003.history" from archive
2023-05-02 08:33:33.745 UTC [21] LOG: restored log file "00000004.history" from archive
2023-05-02 08:33:33.749 UTC [21] LOG: restored log file "00000005.history" from archive
2023-05-02 08:33:33.752 UTC [21] LOG: restored log file "00000006.history" from archive
2023-05-02 08:33:33.755 UTC [21] LOG: restored log file "00000007.history" from archive
2023-05-02 08:33:33.759 UTC [21] LOG: restored log file "00000008.history" from archive
2023-05-02 08:33:33.764 UTC [21] LOG: restored log file "00000009.history" from archive
2023-05-02 08:33:33.771 UTC [21] LOG: redo starts at 0/3000028
2023-05-02 08:33:33.771 UTC [21] LOG: consistent recovery state reached at 0/3000100
2023-05-02 08:33:33.772 UTC [1] LOG: database system is ready to accept read only connections
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
2023-05-02 08:33:34.655 UTC [62] FATAL: role "root" does not exist
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000A0000000000000004': No such file or directory
cp: can't stat '/mnt/server/archivedir/0000000B.history': No such file or directory