On 12/22/2014 04:16 AM, wetter wetterana wrote:
Hi,
I would like to know how I could append a database dump to an existing
database without clearing it.
Example: I got a database 'db' with a tables 't1' including variables
'var1' and 'var2', and table 't2' with variables 'var3' and 'var4'.
Earlier this year, I stored records 1 to 100 in this database and dumped
the whole database to a file 'dump1'. Then I cleared the tables and
began collecting new records 101 - 200. All variables are the same, no
ownership has changed, etc.
Now I would like to append the records 1-100 from dump1 to the existing
database. How could I use pg_dump or another command to append records
1-100 to the existing database? Please note that I do not have enough
space to first recreate the database from the dump and then merge it
with the newer one.
More generally, knowing that I will have to do this periodically, how
can I best dump current records to ease later merging/appending?
Possible solution to follow, but first, why clear records only to append
them later?
Solution:
How did you take the the pg_dump?
If you used the custom format -Fc then you can pull the data for the
table from the dump file by doing something like:
Directly:
pg_restore -t some_table -a -d the_db
Or if you want to look at the data first:
pg_restore -t some_table -a if table_data.sql
Then you can use psql:
psql -d the_db -U some_user -f table_data.sql
If you used the plain text version of pg_dump then you will need to open
the file with a text editor and cut and paste the COPY for the
table to another file and then do the psql command shown above.
<IMPORTANT> Check your primary keys or other unique constraints in the
existing data and the data you are pulling from the dump file for
conflicts<IMPORTANT>
As to your last question, why dump the records? Why not archive them in
the database? Either by having an active flag in the table or by having
an archive table you park them in. Then it becomes an SQL query operation.
Thanks!!
Ana
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
