Re: Backup error - Mailing list pgsql-admin
I suspect your scp operation is not completed and your performing pg_stop_backup before completing scp operation.Hello, I have online backup done as follows:
- pg_start_backup ...
- scp datafiles ...
- pg_stop_backup ...
- scp data archives needed
- Adjust recovery.conf
- Start postgresThe 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.
Use -Cpv option with scp for increasing performance.
Example:
scp -Cpv example.log username@192.168.0.100:.
(pg_start_backup,scp, pg_stop_backup) is proper direction for taking backup.
I suggest you should use pg_basebackup for avoiding any errors.
I suspect your primary key is corrupted, so it might have allowed duplication while inserting records.
Postgres does not duplicates primary key when backup is done via pg_basebackup.
Harshad Adalkonda
Database Administrator
harshad.adalkonda@shreeyansh.com
Office: +919552687400/8400
Attachment
pgsql-admin by date: