From: Adalkonda Harshad <adalkondaharshad@gmail.com>
To: Celso Vieira <sabd.secinfo@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Backup error
Date: Sat, 04 Oct 2014 11:21:23 +0530
Message-ID: <542F8ADB.5040200@gmail.com> (raw)
In-Reply-To: <CANR-yjpTxEdHpqT4nUpfJERH4sjJuaqcGevfEaw57KifuQo9ug@mail.gmail.com>
References: <CANR-yjpTxEdHpqT4nUpfJERH4sjJuaqcGevfEaw57KifuQo9ug@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.comAttachments:ibccccgd.png (image/png, 9.6K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: adalkondaharshad@gmail.com, sabd.secinfo@gmail.com
Subject: Re: Backup error
In-Reply-To: <542F8ADB.5040200@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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