Is there any procedure documented to take incremental backup in multi node environment?

TimescaleDB and PostgreSQL
,
1

Is there a documented procedure for taking incremental backups in a multi-node (self-hosted distributed hyper table ) environment?

If not, could you please advise standard steps? Also, from what I understand, backups should be taken for each access node and data node.

Please make suggestions.

2

@Mohammed_Iliyas_pate,

TimescaleDB has a function for distributed hypertables called create_distributed_restore_point() which can be used in connection with your backup procedures of all data nodes (and access node) to ensure you have a consistent point to restore all nodes to in the event of a failure.

3

Thanks, @ryanbooz I am able to generate a backup file using create_distributed_restore_point(), but the file is always getting generated under the pg_wal folder even though I have configured archive_command in postgresql.config

archive_command = ‘cp %p \%f’

Observation: The generated file gets copied into the archived folder through the command select pg_switch_wal(), but create_distributed_restore_point does copy the generated file to an archive folder.

4

@ryanbooz Thanks for giving solution on this thread, Actually I have a doubt on backup and restore/recovery that is
once I create a distributed restore point and take a base backup And at later point of time may be after some days if I would like to restore and recover the database on some other host until the restore point.
Here how will a dba will identify or know the restore point name from backup to recover. appreciate if you can help me with this!!

5

@naveenpotlapally : I have documented the complete procedure to take incremental backup and restore.

@ryanbooz @LorraineP : Feel free to correct if anything is missed/improved.

  1. Stop Postgres service

  2. Modify config
    wal_level = replica
    archive_mode = on
    archive_command = ‘copy %p C:\ProgramData\archivedir\%f’
    archive_timeout = 600 [ defined wal archive duration]

  3. Create 2 directories : basebackup and WAL Archieve

  4. Start Postgres

  5. Verify: select pg_switch_wal() #it will create incremental wal archieve

  6. Create base Backup:
    pg_basebackup -D C:\ProgramData\basebackup\ -Ft -P

  7. Execute command to note time and number of records:
    a. postgres=# select now();
    now

2022-05-11 13:28:34.24433-07
(1 row)

b. postgres=# select count(*) from locate;
count

 7

(1 row)
8. Create check points,
select pg_switch_wal()
9. Stop postgres.

  1. Rename Postgres data folder and create an empty data folder.

  2. Extract Base Backup
    tar -xvf C:\ProgramData\basebackup\base.tar -C C:\ProgramData\PostgreSQLData\

  3. Create recovery.signal file under data file and add below contents,
    restore_command=‘copy C:\ProgramData\archivedir\%f %p’
    recovery_target_time = ‘2022-05-11 13:25:26’ #Wal archieve time

  4. Update postgresdata recovery flags,
    restore_command = ‘copy C:\ProgramData\archivedir\%f %p’
    recovery_target_time = ‘2022-05-11 13:25:26’ #Wal archieve time

  5. Start Postgres – It restores base backup + Wal archieve

  6. Still database will be in recovery mode, hence run the below command to move out of recovery mode.
    Select pg_wal_replay_resume();

Thank you.

1 Like