pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_basebackup
8+ messages / 7 participants
[nested] [flat]

* pg_basebackup
@ 2019-12-22 09:56  Daulat Ram <Daulat.Ram@exponential.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Daulat Ram @ 2019-12-22 09:56 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hello,

I am taking pg_basebackup of primary db (docker container env.) using the below command :

pg_basebackup -x -h vmzti -U replication -D /etc/postgresql/9.5/main/pg_basebkp/basekp1224 -Ft -z -P
Password:
WARNING:  skipping special file "./postgresql.conf"
WARNING:  skipping special file "./postgresql.conf"
4048512/4048512 kB (100%), 1/1 tablespace



  *   Getting the above warning message.
  *   Also the backup has been completed with only the single file  "tar -xvf base.tar.gz" as an output but there is no  tar -xvf pg_wal.tar.gz file exist.
  *   If we decompress the file  "tar -xvf base.tar.gz"  , I do not see the postmaster.opts file.


Note: We have the different path for the conf files :


data_directory = '/var/lib/postgresql/9.5/main'         # use data in another directory # (change requires restart)
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'       # host-based authentication file # (change requires restart)
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   # ident configuration file # (change requires restart)

postgres=# SHOW config_file;
                 config_file
----------------------------------------------
/var/lib/postgresql/9.5/main/postgresql.conf
(1 row)

postgres=# show hba_file;
               hba_file
--------------------------------------
/etc/postgresql/9.5/main/pg_hba.conf
(1 row)

postgres=#

More details:

postgres@4ed92bac84af:/var/lib/postgresql/9.5/main$ ls -ltr
total 128
-rw------- 1 postgres postgres   208 Sep 13  2017 backup_label.old
drwx------ 8 postgres postgres  4096 Sep 13  2017 base
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_commit_ts
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_dynshmem
drwx------ 4 postgres postgres  4096 Sep 13  2017 pg_multixact
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_replslot
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_serial
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_snapshots
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_stat
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_stat_tmp
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_tblspc
drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_twophase
-rw------- 1 postgres postgres     4 Sep 13  2017 PG_VERSION
-rw------- 1 postgres postgres    88 Sep 13  2017 postgresql.auto.conf
-rwxr-xr-x 1 postgres postgres 22116 Sep 13  2017 postgresql.conf.orig
drwx------ 2 postgres postgres 12288 Sep 13  2017 pg_log
lrwxrwxrwx 1 postgres postgres    38 Sep 13  2017 recovery.conf -> /etc/postgresql/9.5/main/recovery.conf
lrwxrwxrwx 1 postgres postgres    40 Sep 13  2017 postgresql.conf -> /etc/postgresql/9.5/main/postgresql.conf
-rw------- 1 postgres postgres    90 May 21  2019 postmaster.pid
drwx------ 2 postgres postgres  4096 May 21  2019 pg_notify
-rw------- 1 postgres postgres    46 May 21  2019 postmaster.opts
drwx------ 2 postgres postgres  8192 Dec 11 08:10 global
drwx------ 2 postgres postgres  4096 Dec 17 20:12 pg_clog
drwx------ 2 postgres postgres  4096 Dec 17 20:16 pg_subtrans
drwxrwxrwx 2 postgres postgres  4096 Dec 18 08:31 backup
drwx------ 3 postgres postgres  4096 Dec 18 15:56 pg_xlog
drwx------ 4 postgres postgres  4096 Dec 18 16:31 pg_logical
postgres@4ed92bac84af:/var/lib/postgresql/9.5/main$
ident.conf

Please advise.

Thanks,

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_basebackup
@ 2019-12-22 17:49  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Daulat Ram <Daulat.Ram@exponential.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2019-12-22 17:49 UTC (permalink / raw)
  To: Daulat Ram <Daulat.Ram@exponential.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 12/22/19 1:56 AM, Daulat Ram wrote:
> Hello,
> 
> I am taking pg_basebackup of primary db (docker container env.) using 
> the below command :
> 
> pg_basebackup -x -h vmzti -U replication -D 
> /etc/postgresql/9.5/main/pg_basebkp/basekp1224 -Ft -z -P
> 
> Password:
> 
> WARNING:  skipping special file "./postgresql.conf"
> 
> WARNING:  skipping special file "./postgresql.conf"
> 
> 4048512/4048512 kB (100%), 1/1 tablespace


https://www.postgresql.org/docs/9.5/app-pgbasebackup.html

"The backup will include all files in the data directory and 
tablespaces, including the configuration files and any additional files 
placed in the directory by third parties. But only regular files and 
directories are copied. Symbolic links (other than those used for 
tablespaces) and special device files are skipped. (See Section 50.3 for 
the precise details.)"

postgresql.conf is a symlink in the data directory so it is being skipped.

> 
>   * Getting the above warning message.
>   * Also the backup has been completed with only the single file  “tar
>     -xvf base.tar.gz” as an output but there is no  tar -xvf
>     pg_wal.tar.gz file exist.
>   * If we decompress the file  “tar -xvf base.tar.gz”  , I do not see
>     the postmaster.opts file.


Do you have more then one instance of Postgres running?

If so are you certain which one pg_basebackup is being pointed at?

> 
> Note: We have the different path for the conf files :
> 
> data_directory = '/var/lib/postgresql/9.5/main'         # use data in 
> another directory # (change requires restart)
> 
> hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'       # host-based 
> authentication file # (change requires restart)
> 
> ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   # ident 
> configuration file # (change requires restart)
> 
> postgres=# SHOW config_file;
> 
>                   config_file
> 
> ----------------------------------------------
> 
> /var/lib/postgresql/9.5/main/postgresql.conf
> 
> (1 row)
> 
> postgres=# show hba_file;
> 
>                 hba_file
> 
> --------------------------------------
> 
> /etc/postgresql/9.5/main/pg_hba.conf
> 
> (1 row)
> 
> postgres=#
> 
> **
> 
> *More details:*
> 
> postgres@4ed92bac84af:/var/lib/postgresql/9.5/main$ ls -ltr
> 
> total 128
> 
> -rw------- 1 postgres postgres   208 Sep 13  2017 backup_label.old
> 
> drwx------ 8 postgres postgres  4096 Sep 13  2017 base
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_commit_ts
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_dynshmem
> 
> drwx------ 4 postgres postgres  4096 Sep 13  2017 pg_multixact
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_replslot
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_serial
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_snapshots
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_stat
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_stat_tmp
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_tblspc
> 
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_twophase
> 
> -rw------- 1 postgres postgres     4 Sep 13  2017 PG_VERSION
> 
> -rw------- 1 postgres postgres    88 Sep 13  2017 postgresql.auto.conf
> 
> -rwxr-xr-x 1 postgres postgres 22116 Sep 13  2017 postgresql.conf.orig
> 
> drwx------ 2 postgres postgres 12288 Sep 13  2017 pg_log
> 
> lrwxrwxrwx 1 postgres postgres    38 Sep 13  2017 recovery.conf -> 
> /etc/postgresql/9.5/main/recovery.conf
> 
> lrwxrwxrwx 1 postgres postgres    40 Sep 13  2017 postgresql.conf -> 
> /etc/postgresql/9.5/main/postgresql.conf
> 
> -rw------- 1 postgres postgres    90 May 21  2019 postmaster.pid
> 
> drwx------ 2 postgres postgres  4096 May 21  2019 pg_notify
> 
> -rw------- 1 postgres postgres    46 May 21  2019 postmaster.opts
> 
> drwx------ 2 postgres postgres  8192 Dec 11 08:10 global
> 
> drwx------ 2 postgres postgres  4096 Dec 17 20:12 pg_clog
> 
> drwx------ 2 postgres postgres  4096 Dec 17 20:16 pg_subtrans
> 
> drwxrwxrwx 2 postgres postgres  4096 Dec 18 08:31 backup
> 
> drwx------ 3 postgres postgres  4096 Dec 18 15:56 pg_xlog
> 
> drwx------ 4 postgres postgres  4096 Dec 18 16:31 pg_logical
> 
> postgres@4ed92bac84af:/var/lib/postgresql/9.5/main$
> 
> ident.conf
> 
> Please advise.
> 
> Thanks,
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_basebackup
@ 2019-12-23 03:38  Daulat Ram <Daulat.Ram@exponential.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Daulat Ram @ 2019-12-23 03:38 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

thanks Adrian, what about the
postmaster.opts file, this file was also skipped in backup.


We have single  cluster running on the VM.

Thanks.
On 22-Dec-2019 11:19 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/22/19 1:56 AM, Daulat Ram wrote:
> Hello,
>
> I am taking pg_basebackup of primary db (docker container env.) using
> the below command :
>
> pg_basebackup -x -h vmzti -U replication -D
> /etc/postgresql/9.5/main/pg_basebkp/basekp1224 -Ft -z -P
>
> Password:
>
> WARNING:  skipping special file "./postgresql.conf"
>
> WARNING:  skipping special file "./postgresql.conf"
>
> 4048512/4048512 kB (100%), 1/1 tablespace


https://www.postgresql.org/docs/9.5/app-pgbasebackup.html

"The backup will include all files in the data directory and
tablespaces, including the configuration files and any additional files
placed in the directory by third parties. But only regular files and
directories are copied. Symbolic links (other than those used for
tablespaces) and special device files are skipped. (See Section 50.3 for
the precise details.)"

postgresql.conf is a symlink in the data directory so it is being skipped.

>
>   * Getting the above warning message.
>   * Also the backup has been completed with only the single file  “tar
>     -xvf base.tar.gz” as an output but there is no  tar -xvf
>     pg_wal.tar.gz file exist.
>   * If we decompress the file  “tar -xvf base.tar.gz”  , I do not see
>     the postmaster.opts file.


Do you have more then one instance of Postgres running?

If so are you certain which one pg_basebackup is being pointed at?

>
> Note: We have the different path for the conf files :
>
> data_directory = '/var/lib/postgresql/9.5/main'         # use data in
> another directory # (change requires restart)
>
> hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'       # host-based
> authentication file # (change requires restart)
>
> ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   # ident
> configuration file # (change requires restart)
>
> postgres=# SHOW config_file;
>
>                   config_file
>
> ----------------------------------------------
>
> /var/lib/postgresql/9.5/main/postgresql.conf
>
> (1 row)
>
> postgres=# show hba_file;
>
>                 hba_file
>
> --------------------------------------
>
> /etc/postgresql/9.5/main/pg_hba.conf
>
> (1 row)
>
> postgres=#
>
> **
>
> *More details:*
>
> postgres@4ed92bac84af:/var/lib/postgresql/9.5/main$ ls -ltr
>
> total 128
>
> -rw------- 1 postgres postgres   208 Sep 13  2017 backup_label.old
>
> drwx------ 8 postgres postgres  4096 Sep 13  2017 base
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_commit_ts
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_dynshmem
>
> drwx------ 4 postgres postgres  4096 Sep 13  2017 pg_multixact
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_replslot
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_serial
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_snapshots
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_stat
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_stat_tmp
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_tblspc
>
> drwx------ 2 postgres postgres  4096 Sep 13  2017 pg_twophase
>
> -rw------- 1 postgres postgres     4 Sep 13  2017 PG_VERSION
>
> -rw------- 1 postgres postgres    88 Sep 13  2017 postgresql.auto.conf
>
> -rwxr-xr-x 1 postgres postgres 22116 Sep 13  2017 postgresql.conf.orig
>
> drwx------ 2 postgres postgres 12288 Sep 13  2017 pg_log
>
> lrwxrwxrwx 1 postgres postgres    38 Sep 13  2017 recovery.conf ->
> /etc/postgresql/9.5/main/recovery.conf
>
> lrwxrwxrwx 1 postgres postgres    40 Sep 13  2017 postgresql.conf ->
> /etc/postgresql/9.5/main/postgresql.conf
>
> -rw------- 1 postgres postgres    90 May 21  2019 postmaster.pid
>
> drwx------ 2 postgres postgres  4096 May 21  2019 pg_notify
>
> -rw------- 1 postgres postgres    46 May 21  2019 postmaster.opts
>
> drwx------ 2 postgres postgres  8192 Dec 11 08:10 global
>
> drwx------ 2 postgres postgres  4096 Dec 17 20:12 pg_clog
>
> drwx------ 2 postgres postgres  4096 Dec 17 20:16 pg_subtrans
>
> drwxrwxrwx 2 postgres postgres  4096 Dec 18 08:31 backup
>
> drwx------ 3 postgres postgres  4096 Dec 18 15:56 pg_xlog
>
> drwx------ 4 postgres postgres  4096 Dec 18 16:31 pg_logical
>
> postgres@4ed92bac84af:/var/lib/postgresql/9.5/main$
>
> ident.conf
>
> Please advise.
>
> Thanks,
>


--
Adrian Klaver
adrian.klaver@aklaver.com

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_basebackup
@ 2019-12-23 09:11  Kyotaro Horiguchi <horikyota.ntt@gmail.com>
  parent: Daulat Ram <Daulat.Ram@exponential.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Kyotaro Horiguchi @ 2019-12-23 09:11 UTC (permalink / raw)
  To: Daulat.Ram@exponential.com; +Cc: adrian.klaver@aklaver.com; pgsql-general@lists.postgresql.org

Hello.

At Mon, 23 Dec 2019 03:38:12 +0000, Daulat Ram <Daulat.Ram@exponential.com> wrote in 
> thanks Adrian, what about the
> postmaster.opts file, this file was also skipped in backup.

The file is overwritten at startup so there's no point in having it in
a backup. Thus, it is deliberately excluded from a backup.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_basebackup
@ 2019-12-29 16:43  Jeff Janes <jeff.janes@gmail.com>
  parent: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Jeff Janes @ 2019-12-29 16:43 UTC (permalink / raw)
  To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: Daulat Ram <Daulat.Ram@exponential.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@lists.postgresql.org>

On Mon, Dec 23, 2019 at 4:13 AM Kyotaro Horiguchi <horikyota.ntt@gmail.com>
wrote:

> Hello.
>
> At Mon, 23 Dec 2019 03:38:12 +0000, Daulat Ram <Daulat.Ram@exponential.com>
> wrote in
> > thanks Adrian, what about the
> > postmaster.opts file, this file was also skipped in backup.
>
> The file is overwritten at startup so there's no point in having it in
> a backup. Thus, it is deliberately excluded from a backup.
>

That is true and sensible, but it does seem to be contradicted by the
documentation for 9.5.  In later versions, they added "except certain
temporary files managed by PostgreSQL".   That addition seems to just be a
correction of the docs, not something to reflect a change in actual
behavior, at least in regards to postmaster.opts.

Cheers,

Jeff

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* pg_basebackup
@ 2023-11-20 06:30  Matthias Apitz <guru@unixarea.de>
  0 siblings, 2 replies; 8+ messages in thread

From: Matthias Apitz @ 2023-11-20 06:30 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org


Hello,

We're facing in a customer installation (PostgreSQL 13.1 on Linux) the
following problem for the first time and not reproducible:

The effective part of our backup script contains:
...
test -d ${BACKUPWAL}-${DATE}-${NUM}/ || mkdir -p ${BACKUPWAL}-${DATE}-${NUM}/

# kick to archive the current log; use a DB which will exist;
#
psql -U ${DBSUSER} -dpostgres -c "select pg_switch_wal();" > /dev/null

# backup the cluster
#
printf "%s: pg_basebackup the cluster to %s ... " "`date "+%d.%m.%Y-%H:%M:%S"`" ${BACKUPDIR}-${DATE}-${NUM}
${BINDIR}/pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}

...


The resulting stdout/stderr of the script:

16.11.2023-20:20:02: pg_basebackup the cluster to /Backup/postgres/sisis-20231116-1 ... 
pg_basebackup: could not receive data from WAL stream: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_basebackup: child process exited with error 1

pg-error.log:

2023-11-16 20:34:13.538 CET [6250] LOG:  terminating walsender process due to replication timeout

Why the PostgreSQL server says something about "replication", we do
pg_basebackup?

Some more information:

- wal_sender_timeout has default value (60s)
- backup target is a local file, not a network storage
- the Linux SLES 15 server is good equipped
- nothing is logged in /var/log/messages

Any ideas? Thanks.

	matthias


-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_basebackup
@ 2023-11-20 10:01  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Matthias Apitz <guru@unixarea.de>
  1 sibling, 0 replies; 8+ messages in thread

From: Laurenz Albe @ 2023-11-20 10:01 UTC (permalink / raw)
  To: Matthias Apitz <guru@unixarea.de>; pgsql-general@lists.postgresql.org

On Mon, 2023-11-20 at 07:30 +0100, Matthias Apitz wrote:
> We're facing in a customer installation (PostgreSQL 13.1 on Linux) the
> following problem for the first time and not reproducible:

13.1?  Your immediate reaction should be "update to the latest minor release".

> ${BINDIR}/pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}
> 
> The resulting stdout/stderr of the script:
> 
> 16.11.2023-20:20:02: pg_basebackup the cluster to /Backup/postgres/sisis-20231116-1 ... 
> pg_basebackup: could not receive data from WAL stream: server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> pg_basebackup: child process exited with error 1
> 
> pg-error.log:
> 
> 2023-11-16 20:34:13.538 CET [6250] LOG:  terminating walsender process due to replication timeout
> 
> Why the PostgreSQL server says something about "replication", we do
> pg_basebackup?

Because "pg_basebackup" uses a replication connection.

> Some more information:
> 
> - wal_sender_timeout has default value (60s)

Increase "wal_sender_timeout", perhaps to 0 (which means "infinite").

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_basebackup
@ 2023-11-20 11:45  Christoph Moench-Tegeder <cmt@burggraben.net>
  parent: Matthias Apitz <guru@unixarea.de>
  1 sibling, 0 replies; 8+ messages in thread

From: Christoph Moench-Tegeder @ 2023-11-20 11:45 UTC (permalink / raw)
  To: Matthias Apitz <guru@unixarea.de>; +Cc: pgsql-general@lists.postgresql.org

## Matthias Apitz (guru@unixarea.de):

> 2023-11-16 20:34:13.538 CET [6250] LOG:  terminating walsender process due to replication timeout

Besides "what Lauenz said" (especially about the horribly ooutdated
PostgreSQL version): check IO speed and saturation during backup
and make sure you're not stalling. I've seen this beaviour a few
times, mostly in conjunction with btrfs - using a suitably proven
filesystem usually solved the problem (overloaded hardware can
be a problem, too - but modern systems can take quite a bit more
than in the olden days of spinning rust).

Regards,
Christoph

-- 
Spare Space.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-11-20 11:45 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-22 09:56 pg_basebackup Daulat Ram <Daulat.Ram@exponential.com>
2019-12-22 17:49 ` Adrian Klaver <adrian.klaver@aklaver.com>
2019-12-23 03:38   ` Daulat Ram <Daulat.Ram@exponential.com>
2019-12-23 09:11     ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2019-12-29 16:43       ` Jeff Janes <jeff.janes@gmail.com>
2023-11-20 06:30 pg_basebackup Matthias Apitz <guru@unixarea.de>
2023-11-20 10:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-20 11:45 ` Christoph Moench-Tegeder <cmt@burggraben.net>

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