FW: Getting information about sequences
Hello all again,
Thank you for all of your replies,
We are creating a jdbc backup solution that does NOT require the pgdump
program.
We have managed to get it going pretty fast, and the output size at the
moment is rellativelly smaller, but anyway. Most of the key information you
can get from the driver, like primary, foreign keys, but we have a problem
when we restore this information back.
We totally destroy the database being restored and create a new one from
scratch with the information from the backup we took. We need to get the
sequences to the proper value. So at the moment the way we are doing it is
during the backup we get all the sequences from the database, then we get
the primary keys for all the tables, map them using
'table_name'_'id_name'_seq and issue the following command for each sequence
SELECT setval('sequence', (SELECT MAX('id_name') from 'table_name')); during
the restore.
At the moment it works, but it is too sloppy, and apparently not that easy.
As to the temporary table it is not a solution. Because it will not always
be available. The user can backup the info and move to another country(which
is what is happening) and restore this same information on another location.
Part of some requirement.
Once again Thank you all for your replies,
Best Regards,
Fotis
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 15 May 2006 18:11
To: Martijn van Oosterhout
Cc: Forums @ Existanze; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Getting information about sequencesMartijn van Oosterhout <kleptog@svana.org> writes:
On Mon, May 15, 2006 at 10:44:15AM -0400, Tom Lane wrote:
I haven't been able to think of a way to do that, unless
you want to
assume the existence of a plpgsql helper function.
There's an open
request to list last_values in psql's "\ds", and it'd be
real nice to
be able to do it all in one query for that.
Long term I see a few ways of dealing with this:
[ ideas snipped ]Yeah (actually the point about the update-in-place code being relevant
had just occurred to me too). However, none of these are really
desirable solutions from psql's point of view, because they could only
work in 8.2 and later (or whenever we implemented them). It'd be
nicer if \ds still worked against back-rev servers, which means we
need a solution that works with the current server API. I'm thinking
that psql will need to pull the main \ds query result, and then
manually issue a select against each of the sequences (ick). On the
other hand this may be the best thing anyway, since it's entirely
likely that some of those selects would fail for permissions reasons,
and we don't want the whole \ds operation to go down in flames just
because you don't have select rights on one sequence.regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
"Forums @ Existanze" <forums@existanze.com> writes:
We are creating a jdbc backup solution that does NOT require the pgdump
program.
In heaven's name, WHY?
Do you have any idea how much future pain you are setting yourself up
for? pg_dump changes with every PG release to deal with new features
and changes in the system catalogs. Read the CVS logs for pg_dump for
the past few years, then ask yourself if you really want to buy into a
comparable amount of ongoing maintenance effort.
You'd be way better off just invoking pg_dump as a subprocess. If there
are specific features you need that pg_dump doesn't have (eg, dumping
just selected tables) consider helping to add them.
regards, tom lane
Does pg_dump run in Macintosh?
Show quoted text
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 May 2006 17:22
To: Forums @ Existanze
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Getting information about sequences"Forums @ Existanze" <forums@existanze.com> writes:
We are creating a jdbc backup solution that does NOT require the
pgdump program.In heaven's name, WHY?
Do you have any idea how much future pain you are setting
yourself up for? pg_dump changes with every PG release to
deal with new features and changes in the system catalogs.
Read the CVS logs for pg_dump for the past few years, then
ask yourself if you really want to buy into a comparable
amount of ongoing maintenance effort.You'd be way better off just invoking pg_dump as a
subprocess. If there are specific features you need that
pg_dump doesn't have (eg, dumping just selected tables)
consider helping to add them.regards, tom lane
pg_dump runs on any platform postgres does.
On Tue, May 16, 2006 at 05:31:44PM +0300, Forums @ Existanze wrote:
Does pg_dump run in Macintosh?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 May 2006 17:22
To: Forums @ Existanze
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Getting information about sequences"Forums @ Existanze" <forums@existanze.com> writes:
We are creating a jdbc backup solution that does NOT require the
pgdump program.In heaven's name, WHY?
Do you have any idea how much future pain you are setting
yourself up for? pg_dump changes with every PG release to
deal with new features and changes in the system catalogs.
Read the CVS logs for pg_dump for the past few years, then
ask yourself if you really want to buy into a comparable
amount of ongoing maintenance effort.You'd be way better off just invoking pg_dump as a
subprocess. If there are specific features you need that
pg_dump doesn't have (eg, dumping just selected tables)
consider helping to add them.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Here is the thing, the user doesn't have Postgres installed on his/her
computer. So how am I going to distribute pg_dump, from my understandin you
have to compile postgres on a Mac in order to get the pgdump executable. And
is this for every version of Mac?
Show quoted text
-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 16 May 2006 17:46
To: Forums @ Existanze
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Getting information about sequencespg_dump runs on any platform postgres does.
On Tue, May 16, 2006 at 05:31:44PM +0300, Forums @ Existanze wrote:
Does pg_dump run in Macintosh?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 May 2006 17:22
To: Forums @ Existanze
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Getting information about sequences"Forums @ Existanze" <forums@existanze.com> writes:
We are creating a jdbc backup solution that does NOT
require the
pgdump program.
In heaven's name, WHY?
Do you have any idea how much future pain you are setting
yourself
up for? pg_dump changes with every PG release to deal with new
features and changes in the system catalogs.
Read the CVS logs for pg_dump for the past few years, then ask
yourself if you really want to buy into a comparable amount of
ongoing maintenance effort.You'd be way better off just invoking pg_dump as a
subprocess. If
there are specific features you need that pg_dump doesn't
have (eg,
dumping just selected tables) consider helping to add them.
regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.orgso that your
message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/From each according to his ability. To each according to
his ability to litigate.
Forums @ Existanze wrote:
Does pg_dump run in Macintosh?
<sarcasm>
Wait.... you are going to "creating a jdbc backup solution" that does
NOT require the pgdump program, and you don't know if pg_dump runs on
Macintosh?
</>
PostgreSQL operates on every major OS platform. I strongly suggest you
read the docs.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Do you mean every version of OS X? I will not run on OS 9, afaik.
It can be compiled against any version of OS X, again as far as I know.
Gavin
On May 16, 2006, at 7:51 AM, Forums @ Existanze wrote:
Show quoted text
Here is the thing, the user doesn't have Postgres installed on his/her
computer. So how am I going to distribute pg_dump, from my
understandin you
have to compile postgres on a Mac in order to get the pgdump
executable. And
is this for every version of Mac?-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 16 May 2006 17:46
To: Forums @ Existanze
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Getting information about sequencespg_dump runs on any platform postgres does.
On Tue, May 16, 2006 at 05:31:44PM +0300, Forums @ Existanze wrote:
Does pg_dump run in Macintosh?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 May 2006 17:22
To: Forums @ Existanze
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Getting information about sequences"Forums @ Existanze" <forums@existanze.com> writes:
We are creating a jdbc backup solution that does NOT
require the
pgdump program.
In heaven's name, WHY?
Do you have any idea how much future pain you are setting
yourself
up for? pg_dump changes with every PG release to deal with new
features and changes in the system catalogs.
Read the CVS logs for pg_dump for the past few years, then ask
yourself if you really want to buy into a comparable amount of
ongoing maintenance effort.You'd be way better off just invoking pg_dump as a
subprocess. If
there are specific features you need that pg_dump doesn't
have (eg,
dumping just selected tables) consider helping to add them.
regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.orgso that your
message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/From each according to his ability. To each according to
his ability to litigate.
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?