Re: how to append records from dump to existing database?

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(dot)klaver(at)aklaver(dot)com