Re: [GENERAL] Copying data from a table to another database
Dears Craig, Roberts, Reece and Shane Thank you very much for your thoughts. They're helpful! ;-) I've chosen the dump approach to another table in the migration server and then INSERTing the different recs with a WHERE clause for every non-existing timestamp for a deviceID. As far as the pkey was concerned (entry_nbr) it didn't cause me any problems as what's important were the timestamps in the relevant column. Again thanks! Best regards, -- Pedro Doria Meunier [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Copying data from a table to another database
Hi all, This is perhaps a very 'newbie' question but for the life of me, I can't see a way to do it without resorting to programming... (blush) The thing is: I have a table that acts as a historic for some devices activity. Now I've migrated the entire 'shebang' to another server and need to update the history table on the new server with data from the old server for every different record. As the table on the new server grows exponentially (already nearing 1M records) I need to do this asap unless I want the entire thing to take forever... (sweat) Could someone please lend me a hand here? Already thankful for any insight, -- Pedro Doria Meunier [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Copying data from a table to another database
Pedro Doria Meunier wrote: Hi all, This is perhaps a very 'newbie' question but for the life of me, I can't see a way to do it without resorting to programming... (blush) As far as I'm concerned writing SQL is programming. It's not (usually) procedural, but neither is Prolog, ML, Haskell, or any other number of programming languages. Anyway ... your question doesn't seem to be all that specific, but it sounds like you've done a database migration where the data from one table was excluded from the migration. You now wish to copy that table's contents to the new server as well, merging it with the data in the same table that's been created since the migration. If it's just a history table I don't really see what's wrong with doing a data-only dump of just that table using pg_dump, removing any DELETE or TRUNCATE statements from the dump script, then running the load script on the new server. That does assume you reserved space in any primary key ID sequence in the new table though. If there are primary key ID conflicts and you don't actually care about the primary key values you may be able to load the data into a temporary table then do an INSERT...SELECT that generates new primary keys with `nextval' / DEFAULT and copies all other fields. Personally I don't bother putting a primary key on history tables unless it needs to be accessed by an ORM layer or similar, but I realise lots of people do. If all the above completely misses the point then either I've totally misread your question or you might want to explain it in a bit more detail. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying data from a table to another database
You can do this with dblink http://www.postgresql.org/docs/8.3/interactive/contrib-dblink.html pretty easily. Jon -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Pedro Doria Meunier Sent: Sunday, May 25, 2008 6:25 AM To: Postgresql Mailing List Subject: [GENERAL] Copying data from a table to another database Importance: High Hi all, This is perhaps a very 'newbie' question but for the life of me, I can't see a way to do it without resorting to programming... (blush) The thing is: I have a table that acts as a historic for some devices activity. Now I've migrated the entire 'shebang' to another server and need to update the history table on the new server with data from the old server for every different record. As the table on the new server grows exponentially (already nearing 1M records) I need to do this asap unless I want the entire thing to take forever... (sweat) Could someone please lend me a hand here? Already thankful for any insight, -- Pedro Doria Meunier [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying data from a table to another database
On Sun, 2008-05-25 at 12:24 +0100, Pedro Doria Meunier wrote: Now I've migrated the entire 'shebang' to another server and need to update the history table on the new server with data from the old server for every different record. I'm not sure I understand this completely. Would COPY work? For example: $ psql -h oldhost -d olddb -c 'copy schema.table to stdout' | psql -h newhost -d newdb -c 'copy schema.table from stdin' This presumes that a table with the same structure already exists in the new database. If you have made schema changes, or you need only a subset of rows, you can specify an appropriate select statement to the copy command on old database. See documentation for COPY. Also consider a statement like this: = INSERT INTO newtable SELECT * FROM oldtable EXCEPT SELECT * FROM newtable; I'm assuming that you populate a temporary oldtable in the new db (perhaps using the COPY method above). This won't work if there are intentional identical rows in your table. The pipe assumes a Unix-ish box. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] Copying data from a table to another database
Pedro Doria Meunier wrote: Hi all, This is perhaps a very 'newbie' question but for the life of me, I can't see a way to do it without resorting to programming... (blush) something like - pg_dump --data-only --table=myoldhistorytable | psql -h x.x.x.x mynewdb The thing is: I have a table that acts as a historic for some devices activity. Now I've migrated the entire 'shebang' to another server and need to update the history table on the new server with data from the old server for every different record. If I get that right the old server has some of the new data (or more importantly - updates to rows that were transferred) since the transfer started? Your best bet would be to pg_dump the data then change the table name at the start of the file to match a temp table name that can take the old history and then insert/update from the temp table to the new server history table. As the table on the new server grows exponentially (already nearing 1M records) I need to do this asap unless I want the entire thing to take forever... (sweat) Could someone please lend me a hand here? Already thankful for any insight, -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
