0

I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing method for incremental + PITR for postgres 11.5. However, I couldn't find much and found https://www.scalingpostgres.com/tutorials/postgresql-backup-point-in-time-recovery/

meaningful. I started working around the steps mentioned there. However at #add recovery.conf

    # add recovery.conf
    nano /var/lib/postgresql/10/main/recovery.conf 

The article says to create the recovery.conf in main directory. However, as per 11.5 installation, there is no main directory. The directory structure is like:

    /var/lib/pgsql/11/data
    [root@local-pos-52 data]# ls -ltr
      total 96
      -rw-------. 1 postgres postgres 23971 Aug 30 16:05 postgresql.conf
      drwx------. 6 postgres postgres    50 Aug 30 16:07 base
      -rw-------. 1 postgres postgres  4370 Aug 30 16:12 pg_hba.conf
       drwx------. 2 postgres postgres     6 Aug 30 16:12 pg_replslot
       drwx------. 2 postgres postgres    17 Aug 30 19:04 pg_notify
       -rw-------. 1 postgres postgres    58 Aug 30 19:04 postmaster.opts
       -rw-------. 1 postgres postgres    30 Aug 30 19:04 current_logfiles
        drwx------. 2 postgres postgres     6 Aug 30 19:04 pg_stat
        drwx------. 4 postgres postgres    65 Aug 30 19:04 pg_logical
        drwx------. 3 postgres postgres  4096 Aug 30 19:04 pg_wal
       -rw-------. 1 postgres postgres    96 Aug 30 19:04 postmaster.pid
        drwx------. 2 postgres postgres  4096 Aug 30 19:04 global
        drwx------. 2 postgres postgres    60 Aug 30 19:19 pg_stat_tmp
        -rw-------. 1 postgres postgres     3 Aug 30  2019 PG_VERSION
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_twophase
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_tblspc
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_snapshots
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_serial
        drwx------. 4 postgres postgres    34 Aug 30  2019 pg_multixact
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_dynshmem
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_commit_ts
        -rw-------. 1 postgres postgres 23891 Aug 30  2019 postgresql.conf- 
       bak
       -rw-------. 1 postgres postgres    88 Aug 30  2019 
       postgresql.auto.conf
       -rw-------. 1 postgres postgres  1636 Aug 30  2019 pg_ident.conf
      -rw-------. 1 postgres postgres  4269 Aug 30  2019 pg_hba.conf-bak
       drwx------. 2 postgres postgres    17 Aug 30  2019 pg_xact
       drwx------. 2 postgres postgres    17 Aug 30  2019 pg_subtrans
       drwx------. 2 postgres postgres    31 Aug 30  2019 log

Also, there is no folder named "postgresql" got created in this installation at /var/lib location.

I tried to go with creating recovery.conf at this path "/var/lib/pgsql/11/data" only. However, after that when I restarted postgres services,it didn't work.

It will be appreciated ,if someone specifies where to create recovery.conf, so that it will actually work or share the doc/link to implement incremental backups as well as PITR for postgres 11.5

Thanks for any help in advance!

1

That's what you get by following tutorials. It is better to understand the process (as explained in the documentation), then problems like this won't happen.

You have to place recovery.conf in the data directory. The data directory is the directory where postgresql.conf is, unless the data_directory parameter in postgresql.conf says differently.

  • Hi @laurenz albe , thanks for sharing this doc. It was really helpful. Do you have any another step by step doc to implement PITR and incremental backups? – Shiwangini Shishulkar Sep 2 '19 at 4:57
  • Don't look for cookbooks, try to understand the procedure. Knowledge is power. – Laurenz Albe Sep 2 '19 at 5:18
  • I agree with you @laurenz . However, still whenever I place recovery.conf into data directory,my postgresql services aren't starting again. – Shiwangini Shishulkar Sep 2 '19 at 5:39
  • Investigate! Look at the error messages! – Laurenz Albe Sep 2 '19 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.