Hello,
We a situation with a central database that contains the data that needs to be presented at N off-line terminals (N can
be5 000 can be 15 000). Each terminal presents unique data. The central database is used for data preparation. Then the
datafor each terminal is exported as separate SQL file. The terminals run the same application that is used to prepare
thedata - 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
easierto 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
offfor the time of import and then turn them on after the import. However we can turn the triggers off only per table -
sowe 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) -
preservingabsolutely everything about the data - IDs, etc. - that is done using only standard backend commands that are
expectedto 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