pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup error
8+ messages / 5 participants
[nested] [flat]
* Backup error
@ 2014-10-03 09:37 Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 11:00 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-04 05:51 ` Re: Backup error Adalkonda Harshad <adalkondaharshad@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Celso Vieira @ 2014-10-03 09:37 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup error
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
@ 2014-10-03 11:00 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 13:15 ` Re: Backup error Shreeyansh dba <shreeyansh2014@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Albe Laurenz @ 2014-10-03 11:00 UTC (permalink / raw)
To: Celso Vieira *EXTERN* <sabd.secinfo@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup error
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 11:00 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2014-10-03 13:15 ` Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-03 14:17 ` Re: Backup error Celso Vieira <sabd.secinfo@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Shreeyansh dba @ 2014-10-03 13:15 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Celso Vieira *EXTERN* <sabd.secinfo@gmail.com>; pgsql-admin
On Fri, Oct 3, 2014 at 4:30 PM, Albe Laurenz <laurenz.albe@wien.gv.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup error
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 11:00 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 13:15 ` Re: Backup error Shreeyansh dba <shreeyansh2014@gmail.com>
@ 2014-10-03 14:17 ` Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 14:55 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 8+ messages in thread
From: Celso Vieira @ 2014-10-03 14:17 UTC (permalink / raw)
To: Shreeyansh dba <shreeyansh2014@gmail.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; 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:
>
>> 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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup error
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 11:00 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 13:15 ` Re: Backup error Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-03 14:17 ` Re: Backup error Celso Vieira <sabd.secinfo@gmail.com>
@ 2014-10-03 14:55 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 16:25 ` Re: Backup error Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Albe Laurenz @ 2014-10-03 14:55 UTC (permalink / raw)
To: Celso Vieira *EXTERN* <sabd.secinfo@gmail.com>; Shreeyansh dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin
Celso Vieira wrote:
> 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 .
There's the problem.
Without a backup_label file, the backup will be corrupt.
You have to wait until scp is finished before running pg_stop_backup.
What you see is entirely consistent with that.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup error
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 11:00 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 13:15 ` Re: Backup error Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-03 14:17 ` Re: Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 14:55 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2014-10-03 16:25 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-10-03 17:15 ` Re: Backup error Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2014-10-03 16:25 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Celso Vieira *EXTERN* <sabd.secinfo@gmail.com>; pgsql-admin
On Oct 3, 2014, at 8:55 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> You have to wait until scp is finished before running pg_stop_backup.
If, for performance reasons, you want to overlap backup & copy:
1) use rsync during the backup (repeat if it finishes before the backup)
2) after the backup has finished, but before running pg_stop_backup, run rsync again
3) pg_stop_backup
There is a worry about a potential issue with granularity of timestamp on an open file finished rsyncing a split second before finished writing by backup process, there's the -c option for the final rsync pass. But then it's up to you to figure out whether it's actually worth having that extra read of every byte of every file in order to get the head start. Depends on your server I/O load, and relative speed of backup vs copy, and what your actual goal is.
Personally, I do this not for recovery, but for snagging copies for testing, and in that case I don't worry about the -c option.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup error
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 11:00 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 13:15 ` Re: Backup error Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-03 14:17 ` Re: Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 14:55 ` Re: Backup error Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 16:25 ` Re: Backup error Scott Ribe <scott_ribe@elevated-dev.com>
@ 2014-10-03 17:15 ` Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2014-10-03 17:15 UTC (permalink / raw)
To: Celso Vieira <sabd.secinfo@gmail.com>; +Cc: pgsql-admin
On Oct 3, 2014, at 11:12 AM, Celso Vieira <sabd.secinfo@gmail.com> wrote:
> That is, we rely on a backup postgresql online in an environment of high stress and time
> consuming backup ?
If you follow the directions, it works fine. (pg_start_backup, copy files, pg_stop_backup)
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup error
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
@ 2014-10-04 05:51 ` Adalkonda Harshad <adalkondaharshad@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Adalkonda Harshad @ 2014-10-04 05:51 UTC (permalink / raw)
To: Celso Vieira <sabd.secinfo@gmail.com>; pgsql-admin
On 03-10-2014 15:07, 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 suspect your scp operation is not completed and your performing
pg_stop_backup before completing scp operation.
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 <mailto:harshad.adalkonda@shreeyansh.com>
Office: +919552687400/8400
http://www.shreeyansh.com
Attachments:
ibccccgd.png (image/png, 9.6K) download
view image
end of thread, other threads:[~2014-10-04 05:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-03 09:37 Backup error Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 11:00 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 13:15 ` Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-03 14:17 ` Celso Vieira <sabd.secinfo@gmail.com>
2014-10-03 14:55 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-10-03 16:25 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-10-03 17:15 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-10-04 05:51 ` Adalkonda Harshad <adalkondaharshad@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox