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.