responding to Laurenz Albe:
I guess you waited for pg_start_backup to finish before you started copying, right?
-Yes
Where there any error messages from scp?
-No error message in scp , the error appears in -t reindexdb < table_name >
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.
- I also thought the postgres could recover in this case , since it is officially an
online backup. In this case , postgres should know that consuming a backup ,
you might have inconsistency in their datafiles .
Can you verify that the duplicate primary keys do not exist in the original database?
- I checked and they do not exist in the original ,
but is a database of high access and change and it seems to me is the competition that
generates this much access , too much change .
Does the backup generated with scp contain a file "backup_label"? It should.
- No, because when I copy the data has already been generated pg_stop_backup erasing the
backup_label , but already tried unsuccessfully to do the reverse .
Were there any crashes or hardware problems on the original database machine?
- No known flaw , hardware , storage , lan , san are ok .
responding to Venkataramana Aitla:
The primary key is ok in production and the script follows the PostgreSQL documentation .
I appreciate the intervention of you . I try to understand if the postgres performs safely
online backups in high- stress environment. When starting an " init backup " copy the database datafiles or throughout its structure ,
and then a " back end " is expected to apply to the archives , the bank can be integrate .
I understand that a backup can be time consuming inconsistencies due to the time of print,
however the archives should correct it should not ?
2014-10-03 10:15 GMT-03:00 Shreeyansh dba <shreeyansh2014@xxxxxxxxx>:
On Fri, Oct 3, 2014 at 4:30 PM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:Your backup procedure looks correct.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.
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
I suspect that primary key might not working on the table so it might have allowed duplication during insertion.And also I suspect whether script is overriding the backup.Thanks & RegardsVenkataramana Aitla--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
