Home > mailing lists

Thread: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Ankur Kaushik

Date:

15 June 2015, 12:43:06

Hi,

I took Backup using below command

pg_dump "database name" | gzip > /root/dump.sql.gz

While restoring in fresh PostgreSQL server getting below error .

ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

DETAIL: Key (name)=(debug_geocode_address) already exists.

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Albe Laurenz

Date:

15 June 2015, 12:57:59

Ankur Kaushik wrote:
> I took Backup using below command
>  pg_dump "database name" | gzip > /root/dump.sql.gz
> 
> 
> 
> While restoring in fresh PostgreSQL  server getting below error .
> 
> ERROR:  duplicate key value violates unique constraint "geocode_settings_pkey"
> DETAIL:  Key (name)=(debug_geocode_address) already exists.

That looks like data corruption on the original database.

Look at https://wiki.postgresql.org/wiki/Corruption and
follow the "VITALLY IMPORTANT FIRST RESPONSE".

Check if that is the only row that violates the constraint.
You could manually delete all offending rows on the source,
then dump/restore (don't continue working with the database
even if it seems consistent again).

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Ankur Kaushik

Date:

15 June 2015, 14:19:49

I opened the Backup File seen , The duplicate error came in schema : tiger , table : geocode_setting , already data for primary key column exist "debug_geocode_address" primary key value

What is use of tiger schema , is related postGIS . Can we take dump for only schema public ?

On Mon, Jun 15, 2015 at 3:27 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

Ankur Kaushik wrote:
> I took Backup using below command
> pg_dump "database name" | gzip > /root/dump.sql.gz
>
>
>
> While restoring in fresh PostgreSQL server getting below error .
>
> ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"
> DETAIL: Key (name)=(debug_geocode_address) already exists.

That looks like data corruption on the original database.

Look at https://wiki.postgresql.org/wiki/Corruption and
follow the "VITALLY IMPORTANT FIRST RESPONSE".

Check if that is the only row that violates the constraint.
You could manually delete all offending rows on the source,
then dump/restore (don't continue working with the database
even if it seems consistent again).

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Ankur Kaushik

Date:

15 June 2015, 16:14:57

I think this is not a database corrupt , While create a new database there is already Entry in geocode_setting tiger schema . Correct me If I am Wrong .

On Mon, Jun 15, 2015 at 4:49 PM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:

I opened the Backup File seen , The duplicate error came in schema : tiger , table : geocode_setting , already data for primary key column exist "debug_geocode_address" primary key value

What is use of tiger schema , is related postGIS . Can we take dump for only schema public ?

On Mon, Jun 15, 2015 at 3:27 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Ankur Kaushik wrote:
> I took Backup using below command
> pg_dump "database name" | gzip > /root/dump.sql.gz
>
>
>
> While restoring in fresh PostgreSQL server getting below error .
>
> ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"
> DETAIL: Key (name)=(debug_geocode_address) already exists.

That looks like data corruption on the original database.

Look at https://wiki.postgresql.org/wiki/Corruption and
follow the "VITALLY IMPORTANT FIRST RESPONSE".

Check if that is the only row that violates the constraint.
You could manually delete all offending rows on the source,
then dump/restore (don't continue working with the database
even if it seems consistent again).

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Albe Laurenz

Date:

15 June 2015, 18:59:42

Ankur Kaushik wrote:
> Can we take dump for only schema public ?

Yes, see the option "-n".

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Albe Laurenz

Date:

15 June 2015, 19:01:20

Ankur Kaushik wrote:
> I think this is not a database corrupt , While create a new database there is already Entry in
> geocode_setting  tiger schema . Correct me If I am Wrong .

Well, you wrote that you were restoring to a "fresh database server", so I was assuming
that the database was empty.

Preexisting data can of course also lead to this problem.

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Ankur Kaushik

Date:

17 June 2015, 11:58:58

After PostGIS installation what is did executed the below command

-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- Enable US Tiger Geocoder

CREATE EXTENSION postgis_tiger_geocoder;

So there install the new schema tiger , which contains some table and data for Geo.

Now while restoring full database backup from other database server which already contain tiger schema thus it shows duplicate error for tiger schema while restoring the database on blank server.

Consequence is If only Public schema data is restored on other server its shows geometric error even if you have already installed tiger schema .

So is there any way to restore database without any error , or Can we put one line in database backup file , DROP TABLE 'table name' just before CREATE TABLE.

On Mon, Jun 15, 2015 at 9:31 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

Ankur Kaushik wrote:
> I think this is not a database corrupt , While create a new database there is already Entry in
> geocode_setting tiger schema . Correct me If I am Wrong .

Well, you wrote that you were restoring to a "fresh database server", so I was assuming
that the database was empty.

Preexisting data can of course also lead to this problem.

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Albe Laurenz

Date:

17 June 2015, 12:43:34

Ankur Kaushik wrote:
> So is there any way to restore database without any error , or Can we put one line in database backup
> file , DROP TABLE 'table name' just before CREATE TABLE.

You mean like the -c option in pg_dump or pg_restore?

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Ankur Kaushik

Date:

17 June 2015, 13:04:59

Yes I have checked now using -c with pg_dump is right way to take backup and restore . which has worked fine for me.

One more question which is faster way to restore psql < dump.sql or using pg_restore ?

On Wed, Jun 17, 2015 at 3:13 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

Ankur Kaushik wrote:
> So is there any way to restore database without any error , or Can we put one line in database backup
> file , DROP TABLE 'table name' just before CREATE TABLE.

You mean like the -c option in pg_dump or pg_restore?

Yours,
Laurenz Albe

Re: ERROR: duplicate key value violates unique constraint "geocode_settings_pkey"

From

Albe Laurenz

Date:

17 June 2015, 15:16:08

Ankur Kaushik wrote:
> One more question which is faster way to restore psql < dump.sql or using pg_restore ?

That should not be a big difference.  Custom format has the advantage that you can
decide what parts of the dump to restore; you need not know in advance.

Yours,
Laurenz Albe