Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e7QKD-0003lV-R5 for pgsql-admin@arkaria.postgresql.org; Wed, 25 Oct 2017 18:22:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e7QKC-0005Jr-Gl for pgsql-admin@arkaria.postgresql.org; Wed, 25 Oct 2017 18:22:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e7QIK-0001vN-QO for pgsql-admin@postgresql.org; Wed, 25 Oct 2017 18:20:49 +0000 Received: from static.171.15.99.88.clients.your-server.de ([88.99.15.171] helo=reed.cahoots.pl) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e7QIC-0001NK-KI for pgsql-admin@postgresql.org; Wed, 25 Oct 2017 18:20:47 +0000 Received: from localhost (localhost [127.0.0.1]) by reed.cahoots.pl (Postfix) with ESMTP id 30EDF648D6 for ; Wed, 25 Oct 2017 18:20:38 +0000 (UTC) Received: from reed.cahoots.pl ([127.0.0.1]) by localhost (reed.cahoots.pl [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id UOVHLXLwnkpu for ; Wed, 25 Oct 2017 18:20:36 +0000 (UTC) Received: from [192.168.0.135] (89-72-234-139.dynamic.chello.pl [89.72.234.139]) (Authenticated sender: marcin@cahoots.pl) by reed.cahoots.pl (Postfix) with ESMTPSA id ACAF96483A for ; Wed, 25 Oct 2017 18:20:35 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=cahoots.pl; s=reed; t=1508955635; bh=NdgHiFqCaogxve2TOisYzt6V2qsaneASsFuXjJOpbpI=; h=Subject:To:References:From:In-Reply-To; b=d6h/JRlGk+TkvlUwHUzaZ6F6oxt2i7kc287A51zhl0ohznkP8KRIoI1raRfZTFtml v/fyvhKwQzoqwRyZ765xYnrG+7b40N/zBwRCQBMMGeqSVpNXygt4HD5ZDnOrTm9rCH hBonx2RthUj67PA0dW3TQCJ/ycR36fcuGQLSvYho= Subject: Re: Bad recovery: no pg_xlog/RECOVERYXLOG To: "pgsql-admin@postgresql.org" References: <1070071508952614@web46j.yandex.ru> From: Marcin Koziej Message-ID: Date: Wed, 25 Oct 2017 20:20:34 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.4.0 MIME-Version: 1.0 In-Reply-To: <1070071508952614@web46j.yandex.ru> Content-Type: multipart/mixed; boundary="------------C3989BDDECBC4ED3340827F0" Content-Language: en-US List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------C3989BDDECBC4ED3340827F0 Content-Type: multipart/alternative; boundary="------------FE971ADEDC3654B2C04B2947" --------------FE971ADEDC3654B2C04B2947 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit 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" : >> >> 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 >> ) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin --------------FE971ADEDC3654B2C04B2947 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--------------FE971ADEDC3654B2C04B2947-- --------------C3989BDDECBC4ED3340827F0 Content-Type: text/plain; charset=UTF-8; name="backup-base" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="backup-base" IyEvYmluL3NoCiMgLSotIHNoIC0qLQoKaWYgWyAhIC1uICIkU1dJRlRfQ09O VEFJTkVSIiBdOyB0aGVuCiAgICBlY2hvIFNldCBTV0lGVF9DT05UQUlORVIK ICAgIGV4aXQgMTsKZmkKCmlmICEgZ3JlcCAid2FsX2xldmVsW14jXSpyZXBs aWNhIiBwb3N0Z3Jlc3FsLmNvbmYgPi9kZXYvbnVsbDsgdGhlbgogICAgZWNo byAiV0FSTklORzogd2FsX2xldmVsIHNob3VsZCBiZSBzZXQgdG8gcmVwbGlj YSI7CmZpCgpkYXk9YGRhdGUgKyIleS0lbS0lZCJgCnRtcGZpbGU9Ii90bXAv JHtkYXl9LnRneiIKCmN0cmxjKCkKewogICAgcm0gLXYgJHt0bXBmaWxlfQp9 CnRyYXAgJ2N0cmxjJyBTSUdLSUxMIFNJR1RFUk0gU0lHSFVQIFNJR0lOVCBF WElUCgpwZ19iYXNlYmFja3VwIC1GdCAteiAtRCAtID4gJHRtcGZpbGUKCmxz IC1saCAke3RtcGZpbGV9CmlmICEgc3dpZnQgdXBsb2FkIC0tb2JqZWN0LW5h bWUgYGJhc2VuYW1lICR0bXBmaWxlYCAiJHtTV0lGVF9DT05UQUlORVJ9L2Jh c2UvIiAkdG1wZmlsZTsgdGhlbgogICAgZWNobyAiVXBsb2FkaW5nIGZhaWxl ZCIKICAgIGV4aXQgNApmaQoKZXhpdCAwCg== --------------C3989BDDECBC4ED3340827F0 Content-Type: text/plain; charset=UTF-8; name="backup-restore" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="backup-restore" IyEvYmluL3NoCgpzZXQgLWUKCmlmIFsgLXogIiRTV0lGVF9DT05UQUlORVIi IF07IHRoZW4KICBlY2hvIFNldCBTV0lGVF9DT05UQUlORVIKICBleGl0IDE7 CmZpCgpkYXk9JDE7IHNoaWZ0CgppZiBbIC16ICIke2RheX0iIF07IHRoZW4K ICAgZWNobyAic3BlY2lmeSBkYXRlIGluIGZvcm1hdCB5eS1tbS1kZCIKICAg ZXhpdCAxCmZpCmNvbXByZXNzZWQ9Ii90bXAvJHtkYXl9LnRneiIKZGVzdD0k ezE6LS59CgppZiBbIC1uICIkKGxzIC1BICRkZXN0KSIgXTsgdGhlbgogICAg ZWNobyAiZGVzdGluYXRpb24gZGlyZWN0b3J5ICRkZXN0IG11c3QgYmUgZW1w dHkiCiAgICBleGl0IDEKZmkKCmVjaG8gIkRvd25sb2FkaW5nICRwZ19zbWFs bDMvYmFzZS8ke2RheX0udGd6IgppZiAhIHN3aWZ0IGRvd25sb2FkIC1vICRj b21wcmVzc2VkICRTV0lGVF9DT05UQUlORVIgImJhc2UvJHtkYXl9LnRneiIg OyB0aGVuCiAgICBlY2hvICJGYWlsZWQgdG8gZG93bmxvYWQgYmFzZSBiYWNr dXAgJGNvbXByZXNzZWQiCiAgICBleGl0IDIKZmkKdGFyIHh2emYgJGNvbXBy ZXNzZWQKcm0gJGNvbXByZXNzZWQKCmNhdCA+IHJlY292ZXJ5LmNvbmYgPDxF T0YKcmVzdG9yZV9jb21tYW5kID0gJ2JhY2t1cC1yZXN0b3JlLXdhbCAlcCAl ZicKRU9GCgplY2hvICJEb25lLiBDcm9zcyB5b3VyIGZpbmdlcnMgYW5kIHN0 YXJ0IHRoZSBEQiBub3cuIgo= --------------C3989BDDECBC4ED3340827F0 Content-Type: text/plain; charset=UTF-8; name="backup-restore-wal" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="backup-restore-wal" IyEvYmluL3NoCgppZiBbIC16ICIkU1dJRlRfQ09OVEFJTkVSIiBdOyB0aGVu CiAgZWNobyBTZXQgU1dJRlRfQ09OVEFJTkVSCiAgZXhpdCAxOwpmaQoKcGF0 aD0kMTsgc2hpZnQKcmVtb3RlPSQxOyBzaGlmdAppZiBbIC16ICIke3BhdGh9 IiAtbyAteiAiJHtyZW1vdGV9IiBdOyB0aGVuCiAgICBlY2hvICJObyBhcmd1 bWVudCBnaXZlbiIKICAgIGV4aXQgMQpmaQoKY29tcHJlc3NlZD0icGdfeGxv Zy8ke3JlbW90ZX0ubHpvIgpta2RpciAtcCAvdG1wL3BnX3hsb2cKaWYgISBz d2lmdCBkb3dubG9hZCAtbyAiL3RtcC8ke2NvbXByZXNzZWR9IiAkU1dJRlRf Q09OVEFJTkVSICRjb21wcmVzc2VkOyB0aGVuCiAgICBlY2hvICJDYW5ub3Qg ZG93bmxvYWQgL3RtcC8kY29tcHJlc3NlZCIKICAgIGV4aXQgMgpmaQoKaWYg ISBsem9wIC1kYyAiL3RtcC8kY29tcHJlc3NlZCIgPiAkcGF0aDsgdGhlbgog ICAgZWNobyAiQ2Fubm90IHVuY29tcHJlc3MgL3RtcC8kY29tcHJlc3NlZCB0 byAkcGF0aCIKICAgIGV4aXQgMwpmaQoKZXhpdCAwCg== --------------C3989BDDECBC4ED3340827F0 Content-Type: text/plain; charset=UTF-8; name="backup-wal" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="backup-wal" IyEvYmluL3NoCgppZiBbICEgLW4gIiRTV0lGVF9DT05UQUlORVIiIF07IHRo ZW4KICBlY2hvIFNldCBTV0lGVF9DT05UQUlORVIKICBleGl0IDE7CmZpCgpw YXRoPSQxOyBzaGlmdAoKaWYgWyAtbiAiJHBhdGgiIF07IHRoZW4KICBjb21w cmVzc2VkPSIke3BhdGh9Lmx6byIKICBzd2lmdCBzdGF0ICRTV0lGVF9DT05U QUlORVIgJGNvbXByZXNzZWQgMj4vZGV2L251bGwKICBpZiBbICEgJD8gLWVx IDAgXTsgdGhlbgogICAgIGx6b3AgLWMgJHBhdGggPiAkY29tcHJlc3NlZAog ICAgIHN3aWZ0IHVwbG9hZCAkU1dJRlRfQ09OVEFJTkVSICRjb21wcmVzc2Vk CiAgICAgcm0gJGNvbXByZXNzZWQKICBlbHNlCiAgICBlY2hvICJGaWxlICRj b21wcmVzc2VkIGFscmVhZHkgZXhpc3RzIGluICRTV0lGVF9DT05UQUlORVIi CiAgICBleGl0IDEKICBmaQplbHNlCiAgZXhpdCAxCmZpCg== --------------C3989BDDECBC4ED3340827F0 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin --------------C3989BDDECBC4ED3340827F0--