import/export or moving data between databases

Hello,

We a situation with a central database that contains the data that needs to be presented at N off-line terminals (N can be 5 000 can be 15 000). Each terminal presents unique data. The central database is used for data preparation. Then the data for each terminal is exported as separate SQL file. The terminals run the same application that is used to prepare the data - only in user mode. So what we do is - we export the full database structure (incl. triggers and stored procedures) - even if it contains some tables that are not needed at the terminal - this is not a big problem and it is easier to export all - and we export then N times the data for each terminal.

Then at the terminal we drop the old database, create new (this way we are sure that the database is 100% as it must be), import the structure and we import the terminal specific data. The transfer is always central database - > terminal database. There is no information sent back.

The problem we face is -

When we start to import - the triggers are executed - which must not happen. We found a way to turn the triggers off for the time of import and then turn them on after the import. However we can turn the triggers off only per table - so we need the list of tables, but we have not found a reliable way to get it. We can get them from the pg_ system tables - but this means if there is a change in them in next version - we need to change our software, which is not very desirable.

Has anybody similar experience with moving partial data from one database into another (with same database structure) - preserving absolutely everything about the data - IDs, etc. - that is done using only standard backend commands that are expected to survive over many versions of the database.

We use php 4/5 and we can execute shell commands if necessary.

Thank you everybody for the help.

Best,

Iavor

"Iavor Raytchev" <pobox(at)verysmall(dot)org> writes:
> The problem we face is -

> When we start to import - the triggers are executed - which must not happen. We found a way to turn the triggers off for the time of import and then turn them on after the import. However we can turn the triggers off only per table - so we need the list of tables, but we have not found a reliable way to get it. We can get them from the pg_ system tables - but this means if there is a change in them in next version - we need to change our software, which is not very desirable.

It sounds to me like you have reinvented pg_dump ... and not done it
very well. Why don't you just use pg_dump?

regards, tom lane

Dear Tom,

We kind of read all documentation we could find, but that was the only way
we could get -

- export db structure into sql file
- export the records we need into another sql file
- import structure
- turn off triggers
- import the records
- turn on triggers

The main problem is that we export selected records - not the whole database
and not even whole tables. Also we export 5 000 to 15 000 such sets that
have small size - just a fraction of the size of the main database.

Can pg_dump help in that more than we use it?

Best,

Iavor

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Sunday, September 19, 2004 6:45 PM
To: Iavor Raytchev
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] import/export or moving data between databases

"Iavor Raytchev" <pobox(at)verysmall(dot)org> writes:
> The problem we face is -

> When we start to import - the triggers are executed - which must not
happen. We found a way to turn the triggers off for the time of import and
then turn them on after the import. However we can turn the triggers off
only per table - so we need the list of tables, but we have not found a
reliable way to get it. We can get them from the pg_ system tables - but
this means if there is a change in them in next version - we need to change
our software, which is not very desirable.

It sounds to me like you have reinvented pg_dump ... and not done it
very well. Why don't you just use pg_dump?

regards, tom lane

Iavor Raytchev wrote:
> Dear Tom,
>
> We kind of read all documentation we could find, but that was the only way
> we could get -
>
> - export db structure into sql file
> - export the records we need into another sql file
> - import structure
> - turn off triggers
> - import the records
> - turn on triggers
>
> The main problem is that we export selected records - not the whole database
> and not even whole tables. Also we export 5 000 to 15 000 such sets that
> have small size - just a fraction of the size of the main database.
>
> Can pg_dump help in that more than we use it?

Why not select into temp tables as a new database then pg_dump the temp,
followed by restore of temp as though it were the whole thing?

>
> Best,
>
> Iavor
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Sunday, September 19, 2004 6:45 PM
> To: Iavor Raytchev
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] import/export or moving data between databases
>
>
> "Iavor Raytchev" <pobox(at)verysmall(dot)org> writes:
>
>>The problem we face is -
>
>
>> When we start to import - the triggers are executed - which must not
>
> happen. We found a way to turn the triggers off for the time of import and
> then turn them on after the import. However we can turn the triggers off
> only per table - so we need the list of tables, but we have not found a
> reliable way to get it. We can get them from the pg_ system tables - but
> this means if there is a change in them in next version - we need to change
> our software, which is not very desirable.
>
> It sounds to me like you have reinvented pg_dump ... and not done it
> very well. Why don't you just use pg_dump?
>
> regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>