Re: Backup error

Celso Vieira wrote:
> Hello, I have online backup done as follows:
> - pg_start_backup ...
> - scp datafiles ...
> - pg_stop_backup ...
> - scp data archives needed
> - Adjust recovery.conf
> - Start postgres
>
> The backup takes 4 hours.
>
> Bank goes live, but after several mistakes, as do routine reindex on all tables.
>
> But I had followed errors duplication of primary key in the tables, and this prevents the reindex.
> Already tried vacuumdb these cases, but does not solve.
> The index remains active and to query the table, the sql uses the index and returns only one line, but
> when reading the table without index (use id + 1 so it will read the table without index), two values ​
> appear. How can one have 2 primary key values ​​in the table?
> I wonder if it is acceptable to do online backup using pg_start_backup, scp, pg_stop_backup in version
> 9.3.4? This type of backup is correct? Inform you that the same error occurred in the use of
> pg_basebackup in version 9.2.4. I thought it was bug application.
> Summarizing the questions:
> - Is right back up with pg_start_backup, scp, pg_stop_backup?
> - Why postgres duplicates primary keys when the backup is done via scp, and even pg_basebackup?
>
> This operation is made to duplicate the database, but my concern is that our model is not reliable
> backup.

Your backup procedure looks correct.
I guess you waited for pg_start_backup to finish before you started copying, right?
Where there any error messages from scp?
One thing I don't know for sure is how scp behaves when a file it copies is modified
concurrently, so there *might* be a problem.

Can you verify that the duplicate primary keys do not exist in the original database?

Does the backup generated with scp contain a file "backup_label"? It should.

Were there any crashes or hardware problems on the original database machine?

Yours,
Laurenz Albe