postgres backup (pg_basebackup) is failed.

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

postgres backup (pg_basebackup) is failed.

Pavan Kumar-3
Hello Experts,

i am running postgres online backup with pg_basebasckup. and backup is failed for some reason. kindly help me on this .


here are the details: 
postgres version : 11.2

[postgres@pglinux1:/oraworkspace/pg_scripts ] $ psql -V
psql (PostgreSQL) 11.2

pg_basebackup version: 
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -V
pg_basebackup (PostgreSQL) 11.2


os version 

[postgres@pglinux1:/oraworkspace/pg_scripts ] $ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)


backup command 
[postgres@pglinux1:/oraworkspace/pg_scripts ] $
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -h localhost -p 5400 -D /oraworkspace/pgbackup/5400backup --waldir=/oraworkspace/pgbackup/bkpwal_log --checkpoint=fast --wal-method=stream -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 9/5B000028 on timeline 1
pg_basebackup: directory "/oradbaudit/pg_data_tbsp" exists but is not empty
pg_basebackup: removing contents of data directory "/oraworkspace/pgbackup/5400backup"
pg_basebackup: removing contents of WAL directory "/oraworkspace/pgbackup/bkpwal_log"
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ timed out waiting for input: auto-logout


postgres alert log 

2019-05-03 17:16:00.204 GMT [6646] [unknown] [unknown] [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  connection received: host=localhost port=53866
2019-05-03 17:16:00.206 GMT [6646] [unknown] postgres [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  replication connection authorized: user=postgres
2019-05-03 17:16:00.245 GMT [6474]     00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG:  checkpoint starting: immediate force wait
2019-05-03 17:16:00.260 GMT [6474]     00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.003 s, sync=0.000 s, total=0.014 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=16384 kB
2019-05-03 17:16:00.348 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  could not send data to client: Connection reset by peer
2019-05-03 17:16:00.348 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) XX000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT ERROR:  base backup could not send data, aborting backup
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  could not send data to client: Broken pipe
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT FATAL:  connection to client lost
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  disconnection: session time: 0:00:00.145 user=postgres database= host=localhost port=53866



--
Regards,

#!  Pavan Kumar
----------------------------------------------
-
Sr. Database Administrator..!

NEXT GENERATION PROFESSIONALS, LLC
Cell    #  267-799-3182 #  pavan.dba27 (Gtalk)  
India   # 9000459083

Take Risks; if you win, you will be very happy. If you lose you will be Wise  
Reply | Threaded
Open this post in threaded view
|

Re: postgres backup (pg_basebackup) is failed.

Erik Jones-2
This is your problem here:

> pg_basebackup: directory "/oradbaudit/pg_data_tbsp" exists but is not empty

That issue comes from the default format (-F) option of p/plain.  Note the second sentence here from https://www.postgresql.org/docs/11/app-pgbasebackup.html:

Write the output as plain files, with the same layout as the current data directory and tablespaces. When the cluster has no additional tablespaces, the whole database will be placed in the target directory. If the cluster contains additional tablespaces, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server.

Switching that option to use the t/tar format will get around that:

Write the output as tar files in the target directory. The main data directory will be written to a file named base.tar, and all other tablespaces will be named after the tablespace OID.

On Fri, May 3, 2019 at 10:26 AM Pavan Kumar <[hidden email]> wrote:
Hello Experts,

i am running postgres online backup with pg_basebasckup. and backup is failed for some reason. kindly help me on this .


here are the details: 
postgres version : 11.2

[postgres@pglinux1:/oraworkspace/pg_scripts ] $ psql -V
psql (PostgreSQL) 11.2

pg_basebackup version: 
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -V
pg_basebackup (PostgreSQL) 11.2


os version 

[postgres@pglinux1:/oraworkspace/pg_scripts ] $ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)


backup command 
[postgres@pglinux1:/oraworkspace/pg_scripts ] $
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -h localhost -p 5400 -D /oraworkspace/pgbackup/5400backup --waldir=/oraworkspace/pgbackup/bkpwal_log --checkpoint=fast --wal-method=stream -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 9/5B000028 on timeline 1
pg_basebackup: directory "/oradbaudit/pg_data_tbsp" exists but is not empty
pg_basebackup: removing contents of data directory "/oraworkspace/pgbackup/5400backup"
pg_basebackup: removing contents of WAL directory "/oraworkspace/pgbackup/bkpwal_log"
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ timed out waiting for input: auto-logout


postgres alert log 

2019-05-03 17:16:00.204 GMT [6646] [unknown] [unknown] [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  connection received: host=localhost port=53866
2019-05-03 17:16:00.206 GMT [6646] [unknown] postgres [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  replication connection authorized: user=postgres
2019-05-03 17:16:00.245 GMT [6474]     00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG:  checkpoint starting: immediate force wait
2019-05-03 17:16:00.260 GMT [6474]     00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.003 s, sync=0.000 s, total=0.014 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=16384 kB
2019-05-03 17:16:00.348 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  could not send data to client: Connection reset by peer
2019-05-03 17:16:00.348 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) XX000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT ERROR:  base backup could not send data, aborting backup
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  could not send data to client: Broken pipe
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT FATAL:  connection to client lost
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  disconnection: session time: 0:00:00.145 user=postgres database= host=localhost port=53866



--
Regards,

#!  Pavan Kumar
----------------------------------------------
-
Sr. Database Administrator..!

NEXT GENERATION PROFESSIONALS, LLC
Cell    #  267-799-3182 #  pavan.dba27 (Gtalk)  
India   # 9000459083

Take Risks; if you win, you will be very happy. If you lose you will be Wise  


--
Erik Jones
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: postgres backup (pg_basebackup) is failed.

Shreeyansh dba
In reply to this post by Pavan Kumar-3
Hi Pavan,

It looks like your database has tablespace so you can use --tablespace-mapping to modify the destination for storing the tablespaces backup.

Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


On Fri, May 3, 2019 at 10:56 PM Pavan Kumar <[hidden email]> wrote:
Hello Experts,

i am running postgres online backup with pg_basebasckup. and backup is failed for some reason. kindly help me on this .


here are the details: 
postgres version : 11.2

[postgres@pglinux1:/oraworkspace/pg_scripts ] $ psql -V
psql (PostgreSQL) 11.2

pg_basebackup version: 
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -V
pg_basebackup (PostgreSQL) 11.2


os version 

[postgres@pglinux1:/oraworkspace/pg_scripts ] $ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)


backup command 
[postgres@pglinux1:/oraworkspace/pg_scripts ] $
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -h localhost -p 5400 -D /oraworkspace/pgbackup/5400backup --waldir=/oraworkspace/pgbackup/bkpwal_log --checkpoint=fast --wal-method=stream -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 9/5B000028 on timeline 1
pg_basebackup: directory "/oradbaudit/pg_data_tbsp" exists but is not empty
pg_basebackup: removing contents of data directory "/oraworkspace/pgbackup/5400backup"
pg_basebackup: removing contents of WAL directory "/oraworkspace/pgbackup/bkpwal_log"
[postgres@pglinux1:/oraworkspace/pg_scripts ] $ timed out waiting for input: auto-logout


postgres alert log 

2019-05-03 17:16:00.204 GMT [6646] [unknown] [unknown] [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  connection received: host=localhost port=53866
2019-05-03 17:16:00.206 GMT [6646] [unknown] postgres [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  replication connection authorized: user=postgres
2019-05-03 17:16:00.245 GMT [6474]     00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG:  checkpoint starting: immediate force wait
2019-05-03 17:16:00.260 GMT [6474]     00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.003 s, sync=0.000 s, total=0.014 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=16384 kB
2019-05-03 17:16:00.348 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  could not send data to client: Connection reset by peer
2019-05-03 17:16:00.348 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) XX000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT ERROR:  base backup could not send data, aborting backup
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  could not send data to client: Broken pipe
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 08006 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT FATAL:  connection to client lost
2019-05-03 17:16:00.349 GMT [6646] pg_basebackup postgres [unknown] localhost(53866) 00000 5ccc7750.19f6 0 2019-05-03 17:16:00 GMT LOG:  disconnection: session time: 0:00:00.145 user=postgres database= host=localhost port=53866



--
Regards,

#!  Pavan Kumar
----------------------------------------------
-
Sr. Database Administrator..!

NEXT GENERATION PROFESSIONALS, LLC
Cell    #  267-799-3182 #  pavan.dba27 (Gtalk)  
India   # 9000459083

Take Risks; if you win, you will be very happy. If you lose you will be Wise