Home > Uncategorized > FreeBSD 7.0, PostgreSQL 8.3.5 , sed and fkeys on upgrade schema/data of intranet portal

FreeBSD 7.0, PostgreSQL 8.3.5 , sed and fkeys on upgrade schema/data of intranet portal

PostgreSQL 8.3.5 on amd64-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719  [FreeBSD]

FreeBSD 7.0-RELEASE #0: Wed May 21 10:13:51 UTC 2008

in short, the task was to migrate the development database schema to a production one with the production data  .. 🙂

1. you do separated backup – per schema and data.
2. you split the the schema file in two parts  – schema only with no fkeys,  and only fkeys ..
3. create database
4. import the schema file with no fkeys
5. populate  the data
6. import the fkeys only file
7. never sign a rent contract in Mataró when you dont understand a word  in Catalan 🙂
8. its done, it took me 6 minutes to import half a million records , which is not much

the issue I had was the fkeys
solution : use sed .. i just love sed … 10 years I do use sed awk and bash for system administration and so far no perl at all 🙂 😛
cat backup.schema.2009-12-05.00.07.00.sql | sed ‘/^ALTER TABLE ONLY/, /^ *ADD CONSTRAINT .* FOREIGN KEY/!d’ > fkeys.only.sql
cat backup.schema.2009-12-05.00.07.00.sql | sed ‘/^ALTER TABLE ONLY/, /^ *ADD CONSTRAINT .* FOREIGN KEY/d’ > schema.without.fkeys.sql

hope this will save someone ass in upgrading 500 mln db …:)
I do have 120 mln db in fact … its just statistics , and thank to pgsql .. it works like a charm
I have 10 euro until the 1-st Jan 2010, thank to my so called optimism …
i promise this to be my last risky step i take here in Spain. Because I risk my family in fact.
New Year – new luck …
and … frackem them all … So say we all!

Currently listening “WhitesnakeCrying in the Rain

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: