Backup in Postgres

Asked

Viewed 209 times

0

postgres has some system table you can select to find out when the last backup was done just like its status (it ran normal or failed) ?

That is, how to remove as much backup information in postgres ?

EDITED

I’m picking up the backup date of the file generated by pg_basebackup which is in the folder of pg_xlog ( Linux usage). In this folder it creates a file .backup after the end of the process.

The file goes like this :

TART WAL LOCATION: 4/42000028 (file 000000010000000400000042)
STOP WAL LOCATION: 4/42000130 (file 000000010000000400000042)
CHECKPOINT LOCATION: 4/42000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-02-26 13:30:01 BRT
LABEL: pg_basebackup base backup
STOP TIME: 2019-02-26 13:30:19 BRT

I noticed another backup instead of the BRT had -O3. What is that BRT means ?

Can pg_basekacup generate this file even giving some kind of backup error?

postgresql backup postgresql-administration

  • There are several ways to back up Postgresql and only replication, which can be seen as a form of back-up, has record in the system catalog. But it doesn’t seem to be what you’re looking for.

  • How do you back up? It’s Windows Server or Linux?

  • Linux Server. I did it with pg_basebackup.

  • And how you treat the WAL segments?

  • Maybe you are using a wrong backup strategy.

  • Just for test purposes, have you ever tried to restore your BD from one of these "backups"? I understand that you don’t treat WAL segments along with pg_basebackup.

Show 1 more comment

1 answer

2

There is no native table or view in Postgresql for this purpose, but what can be done is a manual solution as follows:

Both the pg_dump.exe (responsible for the backup), regarding the pg_restore.exe (responsible for restoration) can receive parameters. You can make a backup by saving the information in a file .txt and then save these to a table via the command COPY FROM FILE. Example below.

create table test_bkp (
    id integer
);

INSERT INTO test_bkp (id) SELECT g.id
FROM generate_series(1, 10000) AS g (id) ;

--COMANDO DE BACKUP
pg_dump -h localhost -p 5432 -U postgres -Fc -v -t test_bkp -f D:\test_bkp\test_bkp.backup postgres 2> D:\test_bkp\test_bkp.log

--IMPORTANDO INFORMAÇÕES DO BACKUP
create table test_log (
    conteudo text
);

COPY test_log (conteudo) FROM 'D:\\test_bkp\\test_bkp.log'  encoding 'windows-1251';

Another alternative is to run the backup through SQL with the command COPY FROM PROGRAM (only possible with users with certain privileges) and run the pg_dump but is more susceptible to errors in my opinion. This is a simple example, research more about the parameters of pg_dump and pg_restore and on command COPY.

Browser other questions tagged postgresql backup postgresql-administration

You are not signed in. Login or sign up in order to post.