Обсуждение: help for pg_wal issue
Hi,
I want to backup my postgresql database but it fails because it is looking for some wal files on pg_wal directory.
Before backup I checked my pg_wal directory and I saw the wal file,
bash-4.2$ cd /pgdata/pgsql/data/pg_wal
bash-4.2$ ls
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000AE
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000AF
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B0
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B1
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B2
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B3
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B4
-rw------- 1 postgres postgres 16777216 Jun 24 01:16 000000010000000A000000B5
-rw------- 1 postgres postgres 16777216 Jun 24 01:31 000000010000000A000000B6
-rw------- 1 postgres postgres 16777216 Jun 24 01:46 000000010000000A000000B7
But backup fails with,
Failed to open: /pgdata//pgsql/data/pg_wal/000000010000000A000000AE
Error! The backup has failed.
After I checked pg_wal directory and really there is no such file, it was not appear on disk. What can I do for this? Please help..
bash-4.2$ cd /pgdata/pgsql/data/pg_wal
bash-4.2$ ls
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000AF
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B0
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B1
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B2
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B3
-rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B4
-rw------- 1 postgres postgres 16777216 Jun 24 01:16 000000010000000A000000B5
-rw------- 1 postgres postgres 16777216 Jun 24 01:31 000000010000000A000000B6
-rw------- 1 postgres postgres 16777216 Jun 24 01:46 000000010000000A000000B7
-rw------- 1 postgres postgres 16777216 Jun 24 02:01 000000010000000A000000B8
Regards,
Some of my parameters on postgresql.conf file;
wal_buffers = 16MB
max_wal_size = 5GB
min_wal_size = 2GB
shared_buffers = 4GB
max_wal_senders = 10
wal_level = replica
wal_keep_segments = 50
archive_mode = on # enables archiving; off, on, or always
archive_command = 'cp %p /pgdata/arc' # command to use to archive a logfile segment
archive_timeout =0 # force a logfile segment switch after this
Özge Özyavuz |
Profesyonel Hizmetler Mimar |
Ozge.Ozyavuz@softtech.com.tr |
A: Tuzla Teknoloji ve Operasyon Merkezi İçmeler Mah. Piri Reis Cad. No:62 B Blok Kat:Zemin-1 34947 Tuzla / İstanbul Tel : +902165526000 Faks : +902165526597 |
www.softtech.com.tr |
Bu e-posta mesajı ve ekleri gönderildiği kişi ya da kuruma özeldir ve gizlidir. Ayrıca hukuken de gizli olabilir. Hiçbir şekilde üçüncü kişilere açıklanamaz ve yayınlanamaz. Mesajın yetkili alıcısı değilseniz hiçbir kısmını kopyalayamaz, başkasına gönderemez veya hiçbir şekilde kullanamazsınız. Eğer mesajın yetkili alıcısı veya yetkili alıcısına iletmekten sorumlu kişi siz değilseniz, lütfen mesajı sisteminizden siliniz ve göndereni uyarınız. Gönderen ve SOFTTECH A.Ş., bu mesajın içerdiği bilgilerin doğruluğu, bütünlüğü ve güncelliği konusunda bir garanti vermemektedir. Mesajın içeriğinden, iletilmesinden, alınmasından, saklanmasından, gizliliğinin korunamamasından, virüs içermesinden ve sisteminizde yaratabileceği zararlardan şirketimiz sorumlu tutulamaz.
This e-mail and its attachments are private and confidential to the exclusive use of the individual or entity to whom it is addressed. It may also be legally confidential. Any disclosure, distribution or other dissemination of this message to any third party is strictly prohibited. If you are not the intended recipient, you may not copy, forward, send or use any part of it. If you are not the intended recipient or the person who is responsible to transmit to the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and its attachments. The sender and SOFTTECH A.S. do not warrant for the accuracy, currency, integrity or correctness of the information in the message and its attachments. SOFTTECH A.S. shall have no liability with regard to the information contained in the message, its transmission, reception, storage, preservation of confidentiality, viruses or any damages caused in anyway to your computer system.
Вложения
On Mon, 2022-06-27 at 09:50 +0000, Özge Özyavuz wrote: > I want to backup my postgresql database but it fails because it is looking for some wal files on pg_wal directory. > > Before backup I checked my pg_wal directory and I saw the wal file, > > bash-4.2$ cd /pgdata/pgsql/data/pg_wal > bash-4.2$ ls > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000AE > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000AF > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B0 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B1 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B2 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B3 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B4 > -rw------- 1 postgres postgres 16777216 Jun 24 01:16 000000010000000A000000B5 > -rw------- 1 postgres postgres 16777216 Jun 24 01:31 000000010000000A000000B6 > -rw------- 1 postgres postgres 16777216 Jun 24 01:46 000000010000000A000000B7 > > But backup fails with, > > Failed to open: /pgdata//pgsql/data/pg_wal/000000010000000A000000AE > Error! The backup has failed. > > After I checked pg_wal directory and really there is no such file, it was not appear on disk. What can I do for this? Pleasehelp.. > > bash-4.2$ cd /pgdata/pgsql/data/pg_wal > bash-4.2$ ls > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000AF > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B0 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B1 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B2 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B3 > -rw------- 1 postgres postgres 16777216 Jun 24 01:01 000000010000000A000000B4 > -rw------- 1 postgres postgres 16777216 Jun 24 01:16 000000010000000A000000B5 > -rw------- 1 postgres postgres 16777216 Jun 24 01:31 000000010000000A000000B6 > -rw------- 1 postgres postgres 16777216 Jun 24 01:46 000000010000000A000000B7 > -rw------- 1 postgres postgres 16777216 Jun 24 02:01 000000010000000A000000B8 You forgot to tell us how exactly you are performing that backup. Yours, Laurenz Albe
At Mon, 27 Jun 2022 12:28:18 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in > You forgot to tell us how exactly you are performing that backup. Yeah, but Google told me that Veritas may complain with that message. Anywhat the tools is, it seems like that the tool could not continue after it found that a file had been deleted before copying it. That means the server is live. If you take a copy of files from a live server, the backup tool should allow some files are removed or added while copying. If you are taking a base backup (aka online backup), you need to take several subsidiary steps. https://www.postgresql.org/docs/devel/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP If you still see the point, feel free to ask further here but with showing exactly how you are peforming that backup:p regards. -- Kyotaro Horiguchi NTT Open Source Software Center
At Tue, 28 Jun 2022 16:28:31 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in > At Mon, 27 Jun 2022 12:28:18 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in > > You forgot to tell us how exactly you are performing that backup. > > Yeah, but Google told me that Veritas may complain with that > message. Anywhat the tools is, it seems like that the tool could not > continue after it found that a file had been deleted before copying > it. That means the server is live. If you take a copy of files from a > live server, the backup tool should allow some files are removed or > added while copying. > > If you are taking a base backup (aka online backup), you need to take > several subsidiary steps. > > https://www.postgresql.org/docs/devel/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP > - If you still see the point, feel free to ask further here but with Mmm.. My fingers are fat and eyes are slipperly. + If you don't still see the point, feel free to ask further here but with > showing exactly how you are peforming that backup:p > > regards. > > -- > Kyotaro Horiguchi > NTT Open Source Software Center -- Kyotaro Horiguchi NTT Open Source Software Center
Thanks a lot for your reply! We used netbackup for postgresql backups. From what I saw on pgadmin tool backup user first issue this command, SELECT pg_start_backup('label',true) Then tool copy the postgresql directory and its files. Then it executed this command. SELECT pg_stop_backup() what I understand from this document (https://www.postgresql.org/docs/devel/continuous-archiving.html#BACKUP) it shouldbe right below, for backing up all wall files? pg_backup_stop(wait_for_archive => true); Am I right? Thanks a lot. Özge Özyavuz Mimar Profesyonel Hizmetler www.softtech.com.tr _____________________________________________________ Bu e-posta mesajı ve ekleri gönderildiği kişi ya da kuruma özeldir ve gizlidir. Ayrıca hukuken de gizli olabilir. Hiçbirşekilde üçüncü kişilere açıklanamaz ve yayınlanamaz. Mesajın yetkili alıcısı değilseniz hiçbir kısmını kopyalayamaz,başkasına gönderemez veya hiçbir şekilde kullanamazsınız. Eğer mesajın yetkili alıcısı veya yetkili alıcısınailetmekten sorumlu kişi siz değilseniz, lütfen mesajı sisteminizden siliniz ve göndereni uyarınız. Gönderen ve SOFTTECHA.Ş., bu mesajın içerdiği bilgilerin doğruluğu, bütünlüğü ve güncelliği konusunda bir garanti vermemektedir. Mesajıniçeriğinden, iletilmesinden, alınmasından, saklanmasından, gizliliğinin korunamamasından, virüs içermesinden ve sisteminizdeyaratabileceği zararlardan şirketimiz sorumlu tutulamaz. This e-mail and its attachments are private and confidential to the exclusive use of the individual or entity to whom itis addressed. It may also be legally confidential. Any disclosure, distribution or other dissemination of this messageto any third party is strictly prohibited. If you are not the intended recipient, you may not copy, forward, sendor use any part of it. If you are not the intended recipient or the person who is responsible to transmit to the intendedrecipient, please contact the sender by reply e-mail and destroy all copies of the original message and its attachments.The sender and SOFTTECH A.S. do not warrant for the accuracy, currency, integrity or correctness of the informationin the message and its attachments. SOFTTECH A.S. shall have no liability with regard to the information containedin the message, its transmission, reception, storage, preservation of confidentiality, viruses or any damages causedin anyway to your computer system. -----Original Message----- From: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Sent: Tuesday, June 28, 2022 10:32 AM To: laurenz.albe@cybertec.at Cc: Özge Özyavuz <Ozge.Ozyavuz@softtech.com.tr>; pgsql-general@lists.postgresql.org Subject: Re: help for pg_wal issue At Tue, 28 Jun 2022 16:28:31 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in > At Mon, 27 Jun 2022 12:28:18 +0200, Laurenz Albe > <laurenz.albe@cybertec.at> wrote in > > You forgot to tell us how exactly you are performing that backup. > > Yeah, but Google told me that Veritas may complain with that message. > Anywhat the tools is, it seems like that the tool could not continue > after it found that a file had been deleted before copying it. That > means the server is live. If you take a copy of files from a live > server, the backup tool should allow some files are removed or added > while copying. > > If you are taking a base backup (aka online backup), you need to take > several subsidiary steps. > > https://www.postgresql.org/docs/devel/continuous-archiving.html#BACKUP > -LOWLEVEL-BASE-BACKUP > - If you still see the point, feel free to ask further here but with Mmm.. My fingers are fat and eyes are slipperly. + If you don't still see the point, feel free to ask further here but + with > showing exactly how you are peforming that backup:p > > regards. > > -- > Kyotaro Horiguchi > NTT Open Source Software Center -- Kyotaro Horiguchi NTT Open Source Software Center