Обсуждение: Backup error

Поиск
Список
Период
Сортировка

Backup error

От
Celso Vieira
Дата:

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.

Re: Backup error

От
Albe Laurenz
Дата:
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

Re: Backup error

От
Shreeyansh dba
Дата:


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

 
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Backup error

От
Celso Vieira
Дата:
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

 
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


Re: Backup error

От
Albe Laurenz
Дата:
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

Re: Backup error

От
Scott Ribe
Дата:
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
beforefinished writing by backup process, there's the -c option for the final rsync pass. But then it's up to you to
figureout 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
-coption. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: Backup error

От
Scott Ribe
Дата:
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

Re: Backup error

От
Adalkonda Harshad
Дата:

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

Office: +919552687400/8400


http://www.shreeyansh.com

Вложения