Thread: Backup Restore

Well I haven't use much of PGAdmin but I will always be using pg_dump and pg_restore for that as they are really easy to use.

You can find help on backup and restore at --> http://www.postgresql.org/docs/8.2/static/backup.html

--------------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Shoaib Mir wrote:
> Well I haven't use much of PGAdmin but I will always be using pg_dump
> and pg_restore for that as they are really easy to use.
>
> You can find help on backup and restore at -->
> http://www.postgresql.org/docs/8.2/static/backup.html

pgAdmin is just a frontend to pg_dump/pg_restore for backup purposes.
Perhaps Bob could share whatever error message he gets with us?

Oh, and there is no released 1.6.2 version of pgAdmin - that would be
head of the current stable branch.

Regards, Dave.

I'm not getting an error message. The restore utility just doesn't see the
backup file. It's looking for a file *.backup which is there but noot seen.

I am running a version that identifies itself as 1.6.2. I downloaded it a
few weeks ago from the official site. Should I be using an earlier version??

Bob


----- Original Message -----
From: "Dave Page" <dpage@postgresql.org>
To: "Shoaib Mir" <shoaibmir@gmail.com>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Thursday, December 28, 2006 11:30 AM
Subject: Re: [GENERAL] Backup Restore


> Shoaib Mir wrote:
>> Well I haven't use much of PGAdmin but I will always be using pg_dump and
>> pg_restore for that as they are really easy to use.
>>
>> You can find help on backup and restore at -->
>> http://www.postgresql.org/docs/8.2/static/backup.html
>
> pgAdmin is just a frontend to pg_dump/pg_restore for backup purposes.
> Perhaps Bob could share whatever error message he gets with us?
>
> Oh, and there is no released 1.6.2 version of pgAdmin - that would be head
> of the current stable branch.
>
> Regards, Dave.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

Bob Pawley wrote:
> I'm not getting an error message. The restore utility just doesn't see
> the backup file. It's looking for a file *.backup which is there but
> noot seen.

It's a standard file dialogue as provided by your operating system. I
can't think of any reason it wouldn't see the file unless you are
looking in the wrong place.

> I am running a version that identifies itself as 1.6.2. I downloaded it
> a few weeks ago from the official site. Should I be using an earlier
> version??

You must have downloaded an SVN snapshot version. They are available on
the official site, but it's not easy to mistake them for actual release
versions.

Regards, Dave

When I change it to view "all files" it's there - but it won't do anything.

Bob
----- Original Message -----
From: "Dave Page" <dpage@postgresql.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Shoaib Mir" <shoaibmir@gmail.com>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Thursday, December 28, 2006 12:06 PM
Subject: Re: [GENERAL] Backup Restore


> Bob Pawley wrote:
>> I'm not getting an error message. The restore utility just doesn't see
>> the backup file. It's looking for a file *.backup which is there but noot
>> seen.
>
> It's a standard file dialogue as provided by your operating system. I
> can't think of any reason it wouldn't see the file unless you are looking
> in the wrong place.
>
>> I am running a version that identifies itself as 1.6.2. I downloaded it a
>> few weeks ago from the official site. Should I be using an earlier
>> version??
>
> You must have downloaded an SVN snapshot version. They are available on
> the official site, but it's not easy to mistake them for actual release
> versions.
>
> Regards, Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Bob Pawley wrote:
> When I change it to view "all files" it's there - but it won't do anything.

So I assume you've used a different extension than the one the dialogue
is expecting by default?

When you say "it won't do anything." do you mean you cannot select the
file, or that nothing happens when you select it and run the restore? If
the former, does it work if you rename the file to use the expected
extension?

Regards, Dave

Bob Pawley wrote:
> Hi Dave
>
> I can get the restore working if I dump the project spelling out
> "*.backup" and not relying on the default.
>
> However the restore is being aborted due to a pk error for the spatial
> coordinates. I've removed the gis feature from both  applications but
> still get the error.
>
> Any thoughts??

Nope, someone else will need to help with that I'm afraid. The exact
error message would make it infinitely easier to help you though.

Regards, Dave

Following is the error message on pg_restore:-

"pg_restore: ERROR:  duplicate key violates unique constraint
"spatial_ref_sys_pkey"
CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla
1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..."
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  duplicate
key violates unique constraint "spatial_ref_sys_pkey"
CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla
1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..."
pg_restore: *** aborted because of error

Process returned exit code 1."

The GIS feature was removed from the PostgreSQL application before the
project dump.

Anyone have any thoughts on how to get around this??

Bob


----- Original Message -----
From: "Dave Page" <dpage@postgresql.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Shoaib Mir" <shoaibmir@gmail.com>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, December 29, 2006 10:57 AM
Subject: Re: [GENERAL] Backup Restore


> Bob Pawley wrote:
>> Hi Dave
>>
>> I can get the restore working if I dump the project spelling out
>> "*.backup" and not relying on the default.
>>
>> However the restore is being aborted due to a pk error for the spatial
>> coordinates. I've removed the gis feature from both  applications but
>> still get the error.
>>
>> Any thoughts??
>
> Nope, someone else will need to help with that I'm afraid. The exact error
> message would make it infinitely easier to help you though.
>
> Regards, Dave
>

Bob Pawley wrote:
> Following is the error message on pg_restore:-
>
> "pg_restore: ERROR:  duplicate key violates unique constraint
> "spatial_ref_sys_pkey"
> CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla
> 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..."
> pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:
> duplicate key violates unique constraint "spatial_ref_sys_pkey"
> CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla
> 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..."
> pg_restore: *** aborted because of error
>
> Process returned exit code 1."
>
> The GIS feature was removed from the PostgreSQL application before the
> project dump.

Not sure what you mean by that - you removed all GIS related types and
functions from the source database?

> Anyone have any thoughts on how to get around this??

Remove the constraint if you no longer have that requirement. It looks
like you have a primary-key defined on spatial_ref_sys and want to
remove it. See the SQL Reference section of the manuals for how to use
ALTER TABLE to drop primary keys and other constraints.

I'm curious as to how this can happen though. Are the definitions of
table spatial_ref_sys the same in the source and target database?

--
   Richard Huxton
   Archonet Ltd

Hi Dave

I can get the restore working if I dump the project spelling out "*.backup"
and not relying on the default.

However the restore is being aborted due to a pk error for the spatial
coordinates. I've removed the gis feature from both  applications but still
get the error.

Any thoughts??

Bob

----- Original Message -----
From: "Dave Page" <dpage@postgresql.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Shoaib Mir" <shoaibmir@gmail.com>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, December 29, 2006 12:10 AM
Subject: Re: [GENERAL] Backup Restore


> Bob Pawley wrote:
>> When I change it to view "all files" it's there - but it won't do
>> anything.
>
> So I assume you've used a different extension than the one the dialogue is
> expecting by default?
>
> When you say "it won't do anything." do you mean you cannot select the
> file, or that nothing happens when you select it and run the restore? If
> the former, does it work if you rename the file to use the expected
> extension?
>
> Regards, Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Looking through PGAdmin where would I find the spatial references that the
errror message references?

Bob


----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Dave Page" <dpage@postgresql.org>; "Shoaib Mir" <shoaibmir@gmail.com>;
"Postgresql" <pgsql-general@postgresql.org>
Sent: Tuesday, January 02, 2007 2:00 AM
Subject: Re: [GENERAL] Backup Restore


> Bob Pawley wrote:
>> Following is the error message on pg_restore:-
>>
>> "pg_restore: ERROR:  duplicate key violates unique constraint
>> "spatial_ref_sys_pkey"
>> CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla
>> 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..."
>> pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:
>> duplicate key violates unique constraint "spatial_ref_sys_pkey"
>> CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla
>> 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..."
>> pg_restore: *** aborted because of error
>>
>> Process returned exit code 1."
>>
>> The GIS feature was removed from the PostgreSQL application before the
>> project dump.
>
> Not sure what you mean by that - you removed all GIS related types and
> functions from the source database?
>
>> Anyone have any thoughts on how to get around this??
>
> Remove the constraint if you no longer have that requirement. It looks
> like you have a primary-key defined on spatial_ref_sys and want to remove
> it. See the SQL Reference section of the manuals for how to use ALTER
> TABLE to drop primary keys and other constraints.
>
> I'm curious as to how this can happen though. Are the definitions of table
> spatial_ref_sys the same in the source and target database?
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Bob Pawley wrote:
> Looking through PGAdmin where would I find the spatial references that
> the errror message references?

>> Bob Pawley wrote:
>>> Following is the error message on pg_restore:-
>>>
>>> "pg_restore: ERROR:  duplicate key violates unique constraint
>>> "spatial_ref_sys_pkey"
>>> CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000
>>> PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla
>>> 1957",DATUM["Angui..."

Looking at this, you should have a table "spatial_ref_sys" with a
primary key constraint "spatial_ref_sys_pkey"

Find the table via the left-hand tree conrol and its details should be
listed on the right-hand side. The primary key will be detailed at the
top and bottom

--
   Richard Huxton
   Archonet Ltd

Found it in template 1. This seems strange as both servers and pgadmins are
the same version and I haven't opened the template until today.

Bob

----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Dave Page" <dpage@postgresql.org>; "Shoaib Mir" <shoaibmir@gmail.com>;
"Postgresql" <pgsql-general@postgresql.org>
Sent: Wednesday, January 03, 2007 10:44 AM
Subject: Re: [GENERAL] Backup Restore


> Bob Pawley wrote:
>> Looking through PGAdmin where would I find the spatial references that
>> the errror message references?
>
>>> Bob Pawley wrote:
>>>> Following is the error message on pg_restore:-
>>>>
>>>> "pg_restore: ERROR:  duplicate key violates unique constraint
>>>> "spatial_ref_sys_pkey"
>>>> CONTEXT:  COPY spatial_ref_sys, line 1: "2000 EPSG 2000
>>>> PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla
>>>> 1957",DATUM["Angui..."
>
> Looking at this, you should have a table "spatial_ref_sys" with a primary
> key constraint "spatial_ref_sys_pkey"
>
> Find the table via the left-hand tree conrol and its details should be
> listed on the right-hand side. The primary key will be detailed at the top
> and bottom
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Bob Pawley wrote:
> Found it in template 1. This seems strange as both servers and pgadmins
> are the same version and I haven't opened the template until today.

The only thing I can think of is that you accidentally restored into
template1. Probably easier to do with pgadmin than from the command-line.

--
   Richard Huxton
   Archonet Ltd