current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have two Postgres databases on the same server, which have the same schemas. The goal is to have DB1 as the production and DB2, as the database that will receive all the data that is migrated from a MySQL db, and then use the dump created after the migration on DB2 to restore the DB1. In other words, dump DB2 and use this dump to perform a restore on DB1.

This would allow to have the production DB1 "always" available even when the migration process is taking place on DB2.

My question is, is it possible to use the DB2 dump to restore the DB1? Or should a different strategy be used - like renaming the databases? Thank You

share|improve this question

closed as unclear what you're asking by RolandoMySQLDBA, Max Vernon, Mark Storey-Smith, Aaron Bertrand♦ Jun 2 '14 at 13:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
    
Sorry, but it is a little bit unclear. The sentence "The goal ... to restore the DB1" is completely undecipherable, I haven't a shot what the hell are you trying to say. –  peterh May 28 '14 at 16:32
    
Thanks. I'll edit the question but meanwhile the clarification here. I would like to know if it's possible and if there are any constraints or known issues to perform a database restore (on DB1) from a dump that was created from a different database (DB2). –  McRui May 28 '14 at 16:49
1  
Quite clearly you can restore a dump of one database to another database. That is in fact the entire purpose of taking backups in the first place. Having said that, while you are restoring DB1 no user will have access to it, so what is the point of what you are attempting? Unless the restore will take place during off-hours, which begs the question, why not do the migration during off-hours? –  Max Vernon May 29 '14 at 1:27
    
@MaxVernon Thanks. Yes, the purpose is to do a migration process but it takes to long (due to communication issues) and not always it is successful so I need to have at least stable data before users start using the system. So I thought of performing the migration to another db, during working hours and then use it to refresh the production database. –  McRui May 29 '14 at 7:49

1 Answer 1

up vote 2 down vote accepted

It sounds like you're trying to refresh a database that's a snapshot of another DB elsewhere, and do so without interrupting read access to the first DB.

If so, the easiest way is actually to do it live in the snapshot DB. Just begin a transaction, truncate all the tables, and populate them with new data, then commit. Concurrent transactions will see the old data until you commit, then see the new data.

If for some reason you can't do that, you may instead wish to populate one DB offline, then ALTER DATABASE ... RENAME to swap them. Doing this will require a brief period where all connections are forced offline and currently running transactions are aborted, which is why I suggest doing it with the first method above.

If all your tables are in one schema in the main DB you could instead populate a copy of the schema then ALTER SCHEMA ... RENAME to swap them. This doesn't require disconnection.

share|improve this answer
    
Thanks. I did not fully understood the second paragraph. Can you please explain it better? The other suggestions seem to be very good options and will consider them also. –  McRui May 29 '14 at 7:52
    
Well, you're copying data from some other DB? Just BEGIN; TRUNCATE TABLE table1, table2, table3; INSERT INTO table1 .... ; ...; COMMIT; . i.e. in one transaction re-populate the database. –  Craig Ringer May 29 '14 at 7:59
    
Ok. If I'm thinking correctly, it seems like I cannot use that process because of indexes. What I have been doing is a truncate cascade on all tables and start migrating in sequence. –  McRui May 29 '14 at 8:04
    
Sure you can. It's a little less efficient than dropping indexes, truncating, inserting, and re-creating indexes, but it works fine. If it's too slow for you, go for one of the other options - swapping out a schema can be a good choice. –  Craig Ringer May 29 '14 at 10:55
    
Thanks. Yes, I'll test the schema swapping to see if it works better than the other option. –  McRui May 29 '14 at 14:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.