From: Marcin Koziej <marcin@cahoots.pl>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Bad recovery: no pg_xlog/RECOVERYXLOG
Date: Wed, 25 Oct 2017 20:20:34 +0200
Message-ID: <ae7f2ee9-97ff-267e-b6dc-9399250a9314@cahoots.pl> (raw)
In-Reply-To: <1070071508952614@web46j.yandex.ru>
References: <d90474b1-6913-f3ff-d224-73fd931cdd98@cahoots.pl>
<1070071508952614@web46j.yandex.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Now it's fixed, but if anyone needs I'm attaching all scripts to 1)
backup and restore wal's and 2) backup and restore base backup from
OpenStack SWIFT
Marcin Koziej
GPG key: http://go.cahoots.pl/gpg/ Ϟ Twitter: @movonw
On 25.10.2017 19:30, Samed YILDIRIM wrote:
> Hi Marcin,
>
> Could you please share archive_command and restore_command you used?
> If you are using script inside restore or archive comman, please also
> share them. It looks like the problem relevant with them.
>
> Best regards.
> Samed YILDIRIM
>
>
> 25.10.2017, 19:52, "Marcin Koziej" <marcin@cahoots.pl>:
>>
>> Hi!
>>
>> I try to setup continuous archiving with PG 9.6 according to this
>> documentation:
>> https://www.postgresql.org/docs/9.6/static/continuous-archiving.html
>>
>> I have Postgres wal_archive set to replica, I have archive on and
>> archive command is properly copying WAL segments to backup storage.
>>
>> Having this running, I make a successful tar base backup using
>> pg_basebackup.
>>
>> I then stop the DB, remove the data directory, unpack base backup to it,
>> create recovery.conf with a proper restore_command, run the server,
>> and get:
>>
>> LOG: database system was interrupted; last known up at 2017-10-25
>> 15:47:37 UTC
>> LOG: starting archive recovery
>> Object 'pg_small3/pg_xlog/RECOVERYXLOG.lzo' not found
>> Cannot download pg_xlog/RECOVERYXLOG.lzo
>> LOG: invalid checkpoint record
>> FATAL: could not locate required checkpoint record
>> HINT: If you are not restoring from a backup, try removing the file
>> "/var/lib/postgresql/data/backup_label".
>> LOG: startup process (PID 20) exited with exit code 1
>> LOG: aborting startup due to startup process failure
>> LOG: database system is shut down
>>
>> The message about "pg_xlog/RECOVERYXLOG.lzo" is written out by
>> restore_command. Indeed, the file is not in the backup storage, and
>> pg_xlog/RECOVERYXLOG was NEVER sent there by archive_command (which
>> compresses and adds .lzo extension)!
>>
>> What could I be doing wrong?
>>
>>
>> --
>> Marcin Koziej
>>
>>
>> GPG key: http://go.cahoots.pl/gpg/ Ϟ Twitter: @movonw
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
>> <mailto:pgsql-admin@postgresql.org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-adminAttachments:backup-base (text/plain, 565B) download
#!/bin/sh
# -*- sh -*-
if [ ! -n "$SWIFT_CONTAINER" ]; then
echo Set SWIFT_CONTAINER
exit 1;
fi
if ! grep "wal_level[^#]*replica" postgresql.conf >/dev/null; then
echo "WARNING: wal_level should be set to replica";
fi
day=`date +"%y-%m-%d"`
tmpfile="/tmp/${day}.tgz"
ctrlc()
{
rm -v ${tmpfile}
}
trap 'ctrlc' SIGKILL SIGTERM SIGHUP SIGINT EXIT
pg_basebackup -Ft -z -D - > $tmpfile
ls -lh ${tmpfile}
if ! swift upload --object-name `basename $tmpfile` "${SWIFT_CONTAINER}/base/" $tmpfile; then
echo "Uploading failed"
exit 4
fi
exit 0
backup-restore (text/plain, 692B) download
#!/bin/sh
set -e
if [ -z "$SWIFT_CONTAINER" ]; then
echo Set SWIFT_CONTAINER
exit 1;
fi
day=$1; shift
if [ -z "${day}" ]; then
echo "specify date in format yy-mm-dd"
exit 1
fi
compressed="/tmp/${day}.tgz"
dest=${1:-.}
if [ -n "$(ls -A $dest)" ]; then
echo "destination directory $dest must be empty"
exit 1
fi
echo "Downloading $pg_small3/base/${day}.tgz"
if ! swift download -o $compressed $SWIFT_CONTAINER "base/${day}.tgz" ; then
echo "Failed to download base backup $compressed"
exit 2
fi
tar xvzf $compressed
rm $compressed
cat > recovery.conf <<EOF
restore_command = 'backup-restore-wal %p %f'
EOF
echo "Done. Cross your fingers and start the DB now."
backup-restore-wal (text/plain, 526B) download
#!/bin/sh
if [ -z "$SWIFT_CONTAINER" ]; then
echo Set SWIFT_CONTAINER
exit 1;
fi
path=$1; shift
remote=$1; shift
if [ -z "${path}" -o -z "${remote}" ]; then
echo "No argument given"
exit 1
fi
compressed="pg_xlog/${remote}.lzo"
mkdir -p /tmp/pg_xlog
if ! swift download -o "/tmp/${compressed}" $SWIFT_CONTAINER $compressed; then
echo "Cannot download /tmp/$compressed"
exit 2
fi
if ! lzop -dc "/tmp/$compressed" > $path; then
echo "Cannot uncompress /tmp/$compressed to $path"
exit 3
fi
exit 0
backup-wal (text/plain, 439B) download
#!/bin/sh
if [ ! -n "$SWIFT_CONTAINER" ]; then
echo Set SWIFT_CONTAINER
exit 1;
fi
path=$1; shift
if [ -n "$path" ]; then
compressed="${path}.lzo"
swift stat $SWIFT_CONTAINER $compressed 2>/dev/null
if [ ! $? -eq 0 ]; then
lzop -c $path > $compressed
swift upload $SWIFT_CONTAINER $compressed
rm $compressed
else
echo "File $compressed already exists in $SWIFT_CONTAINER"
exit 1
fi
else
exit 1
fi
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: marcin@cahoots.pl
Subject: Re: Bad recovery: no pg_xlog/RECOVERYXLOG
In-Reply-To: <ae7f2ee9-97ff-267e-b6dc-9399250a9314@cahoots.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox