0

On a website I have some scripts which work on a temporary database. Before the scripts are starting I drop and recreate the temporary database from the production database. On the end of the process I would like to update the production database with the results of these scripts from the temporary database, but with keeping the modifications which happened meanwhile the scripts was run (several hour).

I know the replace into is not implemented in the postgreSQL, but I found this solution which will work for me: How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

My problem is that for this solution I have to link the two database together, and as far as I found this also not implemented in the postgreSQL so I have to copy the tables from the temporary database to the live database but I have to change the name of the tables meanwhile to prevent to duplicate values or prevent unwanted changes. I have approximately 30 table but only around half of them are modified by the scripts, so it would be great if I can limit the tables to work on. As far as I found the only method for this to pg_dump from the temporary database into a file and insert them into the production table, but it's not too elegant solution and it would maybe too slow as well.

How can I solve this problem on an effective way?

I have postgreSQL 8.3 on Linux (Ubuntu) and root access.

Community
  • 1
  • 1
lw_lewy
  • 15
  • 4
  • Postgres 8.3 is very old and not maintained anymore. Consider upgrading to Postgres 9+ – Ihor Romanchenko Jul 31 '14 at 14:49
  • Yes I know exactly but the whole applications is very old and it's dangerous to change. I'm the only developer on this project and I'm too busy to work on these kind of tasks which is 'not important' from the clients point of view :(. – lw_lewy Jul 31 '14 at 15:01

1 Answers1

0

Forking the database this way will always generate synchronization issues. Copying databases should be the exception, not the rule.

Instead of creating a database, which is costly, asks for root permissions and may prompt other system problems, you should have your script open a transaction with START TRANSACTION (http://www.postgresql.org/docs/8.3/static/sql-start-transaction.html), do whatever you have to do, and then COMMIT at the end if successful, or ROLLBACK if the script fails.

thebighouse
  • 61
  • 1
  • 2
  • This looks for a good sollution. The problem is that I have a 'collector script' which call subscripts via system call (so via shell), and the sub scripts connects individually to the database. So I can't make a big transaction which cover the whole process, and the first scripts are failing the rest is pointless and can cause errors. Anyway I'll try this solution because at least it'll protect me on the sub-script level and maybe I can make some workaround for the rest. Thank you, I'll accept your answer if it works for me. – lw_lewy Jul 31 '14 at 14:58