Re: Backup error

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(at)gmail(dot)com>:

>
>
> On Fri, Oct 3, 2014 at 4:30 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> wrote:
>
>> 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
>>
>>
> 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 & Regards
> Venkataramana Aitla
> www.shreeyansh.com
>
>
>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>