Обсуждение: postgres backup (pg_basebackup) is failed.
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)
#! 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
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 <pavan.dba27@gmail.com> 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 -Vpsql (PostgreSQL) 11.2pg_basebackup version:[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -Vpg_basebackup (PostgreSQL) 11.2os version[postgres@pglinux1:/oraworkspace/pg_scripts ] $ cat /etc/redhat-releaseRed 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 -vpg_basebackup: initiating base backup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 9/5B000028 on timeline 1pg_basebackup: directory "/oradbaudit/pg_data_tbsp" exists but is not emptypg_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-logoutpostgres alert log2019-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=538662019-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=postgres2019-05-03 17:16:00.245 GMT [6474] 00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG: checkpoint starting: immediate force wait2019-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 kB2019-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 peer2019-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 backup2019-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 pipe2019-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 lost2019-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 # 9000459083Take Risks; if you win, you will be very happy. If you lose you will be Wise
Hi Pavan,
It looks like your database has tablespace so you can use --tablespace-mapping to modify the destination for storing the tablespaces backup.
On Fri, May 3, 2019 at 10:56 PM Pavan Kumar <pavan.dba27@gmail.com> 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 -Vpsql (PostgreSQL) 11.2pg_basebackup version:[postgres@pglinux1:/oraworkspace/pg_scripts ] $ pg_basebackup -Vpg_basebackup (PostgreSQL) 11.2os version[postgres@pglinux1:/oraworkspace/pg_scripts ] $ cat /etc/redhat-releaseRed 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 -vpg_basebackup: initiating base backup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 9/5B000028 on timeline 1pg_basebackup: directory "/oradbaudit/pg_data_tbsp" exists but is not emptypg_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-logoutpostgres alert log2019-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=538662019-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=postgres2019-05-03 17:16:00.245 GMT [6474] 00000 5ccc7743.194a 0 2019-05-03 17:15:47 GMT LOG: checkpoint starting: immediate force wait2019-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 kB2019-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 peer2019-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 backup2019-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 pipe2019-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 lost2019-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 # 9000459083Take Risks; if you win, you will be very happy. If you lose you will be Wise