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

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:

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

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:

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:

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

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:

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:

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