pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql 7.3 versus 7.2
6+ messages / 5 participants
[nested] [flat]
* postgresql 7.3 versus 7.2
@ 2003-02-12 21:02 Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>
0 siblings, 2 replies; 6+ messages in thread
From: Ulf Rehmann @ 2003-02-12 21:02 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; pgsql-interfaces
Hi!
Is there any way to adjust dumps from postgresql 7.3 for use by
version 7.2 and vice versa ?
Or is there a way to transport a full database via csv or in any other
kind to 7.2 and vice versa ?
We are running a couple of servers/mirrors at various locations in the
world, handling data based on postgresql, and now we have trouble to
transport data from and to sites with different versions.
(E.g. http://genealogy.math.ndsu.nodak.edu/
http://genealogy.mathematik.uni-bielefeld.de/ )
Some have upgraded and others cannot immediately follow, since an
upgrade from 7.2 to 7.3 seems to require an upgrade of php4 and maybe
even of apache, which takes time etc.
So it would be helpful to have an easy tool to convert between data
for both versions.
Any help or recommendation is very much appreciated!
Best regards,
Ulf Rehmann
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgresql 7.3 versus 7.2
@ 2003-02-13 06:43 .@babolo.ru
parent: Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>
1 sibling, 0 replies; 6+ messages in thread
From: . @ 2003-02-13 06:43 UTC (permalink / raw)
To: Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>; +Cc: pgsql-hackers@postgresql.org; pgsql-interfaces
> Is there any way to adjust dumps from postgresql 7.3 for use by
> version 7.2 and vice versa ?
>
> Or is there a way to transport a full database via csv or in any other
> kind to 7.2 and vice versa ?
>
> We are running a couple of servers/mirrors at various locations in the
> world, handling data based on postgresql, and now we have trouble to
> transport data from and to sites with different versions.
>
> (E.g. http://genealogy.math.ndsu.nodak.edu/
> http://genealogy.mathematik.uni-bielefeld.de/ )
>
> Some have upgraded and others cannot immediately follow, since an
> upgrade from 7.2 to 7.3 seems to require an upgrade of php4 and maybe
> even of apache, which takes time etc.
>
> So it would be helpful to have an easy tool to convert between data
> for both versions.
>
> Any help or recommendation is very much appreciated!
I live with PostgreSQL from 6.5 and for
independance every my application write
in such a manner:
database description is written using m4 macros.
short example (<qb><qe> - quote open and quote close - they are unprintable):
CREATE_SECTION<qb><qe>dnl
CREATE_TABLE(istat_d, rs, normal)
( date date NOT NULL
, wclass int NOT NULL
, count int8 NOT NULL
)
INHERITS(ifaces)
;
TUNE_SECTION<qb><qe>dnl
CREATE UNIQUE INDEX istat_d_idx ON istat_d(router, iface, date, wclass);
ALTER TABLE istat_d ADD FOREIGN KEY(router) REFERENCES router;
ALTER TABLE istat_d ADD FOREIGN KEY(router, iface) REFERENCES iface;
COMMENT ON TABLE istat_d IS '????? ? ???????';
COMMENT ON COLUMN istat_d.date IS '????? ??????';
END_SECTION<qb><qe>dnl
m4 creates different files:
for shema creation:
CREATE TABLE istat_d
( date date NOT NULL
, wclass int NOT NULL
, count int8 NOT NULL
)
INHERITS(ifaces)
;
for content restore (:1 - directory):
\set istat_d '\\copy istat_d FROM \'' :1 '/istat_d\''
:istat_d
for last pass (I call it 'tune'):
GRANT SELECT ON istat_d TO ispdb;
GRANT INSERT ON istat_d TO ispdb;
GRANT SELECT ON istat_d TO GROUP ispdbuser;
CREATE UNIQUE INDEX istat_d_idx ON istat_d(router, iface, date, wclass);
ALTER TABLE istat_d ADD FOREIGN KEY(router) REFERENCES router;
ALTER TABLE istat_d ADD FOREIGN KEY(router, iface) REFERENCES iface;
COMMENT ON TABLE istat_d IS '????? ? ???????';
COMMENT ON COLUMN istat_d.date IS '????? ??????';
for content dump:
\set istat_d '\\copy istat_d TO \'' :1 '/istat_d\''
:istat_d
for shema deletion:
DROP TABLE istat_d;
for empty database initialisation - void in this example.
'?' in example are symbols of my language.
So every database replication is:
- content dump (not PostgreSQL dump)
- shema creation
- content restore
- tune pass
This unlock me from PostgreSQL version and ease me
to upgrade application version (with possible
'up' pass in addition)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgresql 7.3 versus 7.2
@ 2003-02-13 13:49 Robert Treat <xzilla@users.sourceforge.net>
parent: Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>
1 sibling, 2 replies; 6+ messages in thread
From: Robert Treat @ 2003-02-13 13:49 UTC (permalink / raw)
To: Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>; +Cc: pgsql-hackers@postgresql.org; pgsql-interfaces
On Wed, 2003-02-12 at 16:02, Ulf Rehmann wrote:
>
> Hi!
>
> Is there any way to adjust dumps from postgresql 7.3 for use by
> version 7.2 and vice versa ?
>
> Or is there a way to transport a full database via csv or in any other
> kind to 7.2 and vice versa ?
>
> We are running a couple of servers/mirrors at various locations in the
> world, handling data based on postgresql, and now we have trouble to
> transport data from and to sites with different versions.
>
Create a schema only dump with the 7.2 pg_dump.
Create a data only dump (possibly with INSERT output) with the 7.3
pg_dump. Assuming you haven't done anything too wacky, you should be
able to drop your 7.2 database, reload the 7.2 schema, then load up the
7.3 data. As always, test this out before doing it on a production
system.
Robert Treat
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgresql 7.3 versus 7.2
@ 2003-02-13 14:14 Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>
parent: Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 0 replies; 6+ messages in thread
From: Ulf Rehmann @ 2003-02-13 14:14 UTC (permalink / raw)
To: xzilla@users.sourceforge.net; +Cc: pgsql-hackers@postgresql.org; pgsql-interfaces
| Create a schema only dump with the 7.2 pg_dump.
| Create a data only dump (possibly with INSERT output) with the 7.3
| pg_dump. Assuming you haven't done anything too wacky, you should be
| able to drop your 7.2 database, reload the 7.2 schema, then load up the
| 7.3 data. As always, test this out before doing it on a production
| system.
|
Thanks a lot for this quick explanation, this is very helpful!
By the way, is there a possibility to just do incremental
dumps/restores? Say, since a given date?
Ulf
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [HACKERS] postgresql 7.3 versus 7.2
@ 2003-02-13 15:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-02-13 15:42 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>; pgsql-hackers@postgresql.org; pgsql-interfaces
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Wed, 2003-02-12 at 16:02, Ulf Rehmann wrote:
>> Is there any way to adjust dumps from postgresql 7.3 for use by
>> version 7.2 and vice versa ?
> Create a schema only dump with the 7.2 pg_dump.
> Create a data only dump (possibly with INSERT output) with the 7.3
> pg_dump. Assuming you haven't done anything too wacky, you should be
> able to drop your 7.2 database, reload the 7.2 schema, then load up the
> 7.3 data. As always, test this out before doing it on a production
> system.
I think he'll have trouble even with that :-( ... 7.3 pg_dump will try
to use column-name-list syntax in its COPY commands, which the 7.2
server won't like.
AFAICS the only answer is to run the data dump through a filter (sed or
perl or whatever) to edit the COPY commands to a format 7.2 will take.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [HACKERS] postgresql 7.3 versus 7.2
@ 2003-02-13 16:31 Neil Conway <neilc@samurai.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Neil Conway @ 2003-02-13 16:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>; PostgreSQL Hackers <pgsql-hackers@postgresql.org>; pgsql-interfaces
On Thu, 2003-02-13 at 10:42, Tom Lane wrote:
> I think he'll have trouble even with that :-( ... 7.3 pg_dump will try
> to use column-name-list syntax in its COPY commands, which the 7.2
> server won't like.
If you used an insert-only, data-only dump, that might work...
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-02-13 16:31 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-12 21:02 postgresql 7.3 versus 7.2 Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>
2003-02-13 06:43 ` .@babolo.ru
2003-02-13 13:49 ` Robert Treat <xzilla@users.sourceforge.net>
2003-02-13 14:14 ` Ulf Rehmann <rehmann@mathematik.uni-bielefeld.de>
2003-02-13 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-13 16:31 ` Neil Conway <neilc@samurai.com>
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