PostgreSQL - Transfer data from one db to another

Question

I have an original database[1] in which I deleted quite a lot of data which I didn't intend to by using a bad query.

I have created a backup database[2] that is a copy of the original database[1] I deleted from, before I deleted anything. Now I want to move the data that was wrongfully deleted, from my backup database[2] to my original database[1]

I need to make sure that no duplicates are created in this process as some of the data is still in my original database[1].

My databases have the structure:

-----------------------------------------------------
| id (serial - auto incrementing int) | - primary key
| did (varchar) |
| sid (int) |
| timestamp (bigint) |
| data (json) | 
| db_timestamp (bigint) |
-----------------------------------------------------

I have tried finding a solution by Googling, but to no avail. Based on my SQL knowledge I don't think this can be done (from one db to another), but I am quite ready to implement a Python script, if that is what it takes (I am quite well rounded in Python). I am running PostgreSQL 9.6 and I am using pgAdmin 3 to write queries to my DB.

The table I need to transfer from is called datastore and I hope that someone has a good idea of how to perform this data transfer, without creating duplicates.

I hope I got every detail nailed down, if not, let me know and I will provide it.

UPDATE

I guess it would be a good idea to mention that the problem that I encounted on my original database[1] was that I deleted a little to much data. The rows I want transferred are the rows that I accidentally deleted.

So I assume that it would be good enough to check if a the id of each row exists already. If it does I should just skip the row in question and if not I should transfer the row.

Any suggestions are welcome! I am not the brightest SQL hawk ;)

you can use COPY from db to csv abd then from csv to a another db. or use dblink for it. But from you rescription I have a feeling you speak of table when you say database. Please update post with backup and restore command you used — Vao Tsun
– Vao Tsun, Commented Sep 4, 2017 at 7:30
No. I am talking about databases. Not tables. I have two databases. The backup database is an AWS backup of the other one, my original one. Both databases are running on different virtual machines if I am not wrong. And thanks for the reply. I will see what I can do with it ;) — Zeliax
– Zeliax, Commented Sep 4, 2017 at 7:33
with aws you need to set up connection (security group? subnet? again depends on if its RDS or EC2) to use dblink or COPY to local disk. again, please show us the backup command you used - it will very much help to uderstand your setup — Vao Tsun
– Vao Tsun, Commented Sep 4, 2017 at 7:41
@Zeliax If there is a way to identify duplicates that doesn't involve the primary key, then you could do simple select and insert. Depending on how the data ended up in your tables, your timestamp column might be a good candidate to place a unique constraint, rejecting any duplicates. — bgse
– bgse, Commented Sep 4, 2017 at 7:47
in case you have two rds - both copy and dblink would work for you — Vao Tsun
– Vao Tsun, Commented Sep 4, 2017 at 8:23

Vao Tsun · Accepted Answer · 2017-09-04 08:29:56Z

1

you can use copy for that (in psql -h db2_restored on local machine):

\copy datastore to '/tmp/d.csv'

then on local machine psql -h db1_original:

create table tt as select * from datastore where false;
\copy tt from '/tmp/d.csv'
insert into datastore 
  select * 
  from tt 
  join datastore orig on tt.id = orig.id 
  where orig.id is null
;

it will go over network to your machine and back to RDS, and from this point is inefficient, yet using dblink will require changes (create extension, config RDS to talk to each other and so on). And if you choose dblink - logic remains the same - use PK to identify missing keys

answered Sep 4, 2017 at 8:29

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zeliax Over a year ago

Thanks I will try this. Just one question though. Where should I run the \copy command from? Ohh. I think I got it. I should write the following in cmd on my restored db: psql -h db2_restored \copy datastore to '/tmp/d.csv'?

Vao Tsun Over a year ago

if you use psql's \copy - run it on any client. RDS will not allow you run psql as it has no shell, so you have to run it on your laptop for instance

Collectives™ on Stack Overflow

PostgreSQL - Transfer data from one db to another

UPDATE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related