Re: Backup error

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(at)192(dot)168(dot)0(dot)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(dot)adalkonda(at)shreeyansh(dot)com <mailto:harshad(dot)adalkonda(at)shreeyansh(dot)com>

Office: +919552687400/8400

http://www.shreeyansh.com