Re: Backup error - Mailing list pgsql-admin
| From | Celso Vieira |
|---|---|
| Subject | Re: Backup error |
| Date | |
| Msg-id | CANR-yjpE0yQTuaneNJV4-i4d+zrDRpUWwu87Lhx5UG1vJZv5xQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Backup error (Shreeyansh dba <shreeyansh2014@gmail.com>) |
| Responses |
Re: Backup error
|
| List | pgsql-admin |
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@gmail.com>:
On Fri, Oct 3, 2014 at 4:30 PM, Albe Laurenz <laurenz.albe@wien.gv.at> 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 AlbeI 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
pgsql-admin by date: