Обсуждение: two databases in one
Hello, I'm working with a postgresql 7.4.7 I've got two databases with the same tables and schema on two different servers. I want to have only one database. So i would like to insert in the first database the data of the second database. I don't know how to do this because almost of the tables contains serial. It's for production databases, i can't make mistakes. Can anyone help me? It's urgent. Thanks
Вложения
> I've got two databases with the same tables and schema on two different > servers. > I want to have only one database. > So i would like to insert in the first database the data of the second > database. > I don't know how to do this because almost of the tables contains serial. > It's for production databases, i can't make mistakes. Maybe you could just import the second database into another schema of the first database if it's urgent... Otherwise the nature of conflicts will have to be thought through, like - Are there some data that exist in both databases - i.e. which become duplicates if both tables are merged? - Are the serials used as foreign keys in other tables or as the basis of any other fields? - Can the serials be changed to different numbers - e.g., is it ok or even needed to convert "11" on database B to "10011" on database A and so on, to avoid conflict two ID 11 records having different data? - Does the order of records matter? Once the problem is familiar, many gurus will be able to help. I just wanted to provide a starter. Regards, Ben K. Developer http://benix.tamu.edu
Thanks for your response. Indeed , the serials are used as foreign keys. We made a web site with a database. Then we duplicated the web site and the databse (without data, only schéma) for another server . The 2 databases were filled in the same time. Now someone would like to merge the 2 databases and have only 1 site and 1 database. I was thinking about changing serials of the 2nd database and do a restore on the 1st, as you said. But it's a big database, and i'm not sure of the exactly things to do. Ben K. a écrit : >> I've got two databases with the same tables and schema on two >> different servers. >> I want to have only one database. >> So i would like to insert in the first database the data of the >> second database. >> I don't know how to do this because almost of the tables contains >> serial. >> It's for production databases, i can't make mistakes. > > Maybe you could just import the second database into another schema of > the first database if it's urgent... > > Otherwise the nature of conflicts will have to be thought through, like > > - Are there some data that exist in both databases - i.e. which become > duplicates if both tables are merged? > > - Are the serials used as foreign keys in other tables or as the basis > of any other fields? > > - Can the serials be changed to different numbers - e.g., is it ok or > even needed to convert "11" on database B to "10011" on database A and > so on, to avoid conflict two ID 11 records having different data? > > - Does the order of records matter? > > > Once the problem is familiar, many gurus will be able to help. I just > wanted to provide a starter. > > > Regards, > > > Ben K. > Developer > http://benix.tamu.edu > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
Вложения
Soulabaille Samantha wrote: > Hello, > > I'm working with a postgresql 7.4.7 > I've got two databases with the same tables and schema on two different > servers. > I want to have only one database. > So i would like to insert in the first database the data of the second > database. > I don't know how to do this because almost of the tables contains serial. > It's for production databases, i can't make mistakes. > > Can anyone help me? It's urgent. Whatever you do, I would suggest doing it in a test environment first to work out any problems. You can also get some ideas of the time required and possibly tune the process. And of course make a good backup first. I would examine all the tables to determine whether there is likely to be an underlying data collision (duplicate id in both places where unique is required). If the only place that is likely to happen is with sequences then first make a list of them: select * from pg_class where relkind = 'S'; You will need to "make room" in the main tables for the data from the tables from the other database. I would look at the tables influenced by the sequence in question like this: select min(some_serial), max(some_serial), max(some_serial)-min(some_serial) as range from some_table; -[ RECORD 1 ]---- min | 104973931 max | 111209210 range | 6235279 So you need a gap of 6235279 ids in the target table so check the status of the target sequence then advance it as appropriate - give yourself whatever cushion is appropriate given how rapidly the sequence is used. select * from some_sequence ; -[ RECORD 1 ]-+-------------------- sequence_name | some_sequence last_value | 111209231 increment_by | 1 max_value | 9223372036854775807 min_value | 100000000 cache_value | 1 log_cnt | 25 is_cycled | f is_called | t select setval('some_sequence', 111209231 + 6500000); Then you need to move the source data sequences so they will land in the gap so in this case they need to be moved forward by 111209231-104973931+1 so run the appropriate updates on the source database. This is a basic example which assumes you can shut down the source database (sequences stop incrementing) prior to the transfer. You may also need to examine multiple tables if they all rely on the same sequence. You will need to also determine the order in which to put data back into the target server in order to avoid violating foreign-key restrictions. Sounds like a headache. Good luck. Cheers, Steve
On 16.08.2006, at 9:13 Uhr, Soulabaille Samantha wrote: > I've got two databases with the same tables and schema on two > different servers. > I want to have only one database. Okay. > I don't know how to do this because almost of the tables contains > serial. And you have overlapping keys from that? Not nice. > It's for production databases, i can't make mistakes. NEVER try such a thing on a production DB. Always test in a clean room test environment! I have done this once, but it was a big hazzle. What I've done: 0. Dump production and create a test server. Work on that for now. 1. Add columns to db1 where you can temporarily store the keys needed to build relationships (like orig_pk, orig_fk_other_table). 2. Drop foreign key constraints if they prevent you from inserting rows without doing relationships. Drop existing "not null" constraints from foreign key columns. 3. Read the content of the db2 into db1 while doing the following: insert with NEW primary keys and empty foreign keys, store necessary orig_pk and original foreign keys in the temp columns. 4. Iterate over the the newly inserted rows and insert the correct foreign keys for the new db by traversing the old relationships with the information you have stored in the temp columns. 5. Re-create foreign key constraints. Re-create "not null" constraints. 6. Drop the tmp columns. 7. Test your result carefully. 8. Be absolutely sure, it contains, what you want it to contain. Check again. ====> Build a script with all steps, so that it runs automatically. Test it again and again! 9. Shut down your production environment for a service window. 10. Backup your production environment. 11. Apply your script (yes, you should have build a script for that!!!!) to the production server. 12. Test again the result. 13. Test again. 14. Bring your service back online. No guarantee that this will work for you. You will also get real fun if you have n:m join tables in your database. Also you may want to build special handling if you have non-unique values (like product categories, usergroups, users, ...) in your dbs: you might not want to have duplicates for them. Build special handling for that. Good case to show, when primary keys that are absolutely unique and not only unique in one db would have helped very much. Therefor I like the ugly 24 byte unique pks from WebObjects very much ... cug
Please unsubscribe Regards R Mohan Blue Star Infotech Limited T : +91-22-66956969