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 “Whitesnake – Crying in the Rain“
- 20 ways to organize all your stuff March 25, 2017Gadgets The sweet satisfaction of having everything in its place. For those of us yearning for the sweet satisfaction of having everything in its place. Read on.Billy Cadden
- US and UK laptop bans on some Middle East flights come into effect March 25, 2017The US and UK bans affect direct flights from some Middle East airports.
- DNA-bothering eggheads brew beer you were literally born to likeRed alert: Science mixed with marketing detected London-based Meantime Brewing Company, acquired a year ago by Belgian beverage multinational Anheuser-Busch InBev, wants to sell you beer tuned to your taste.…Thomas Claburn
- Astroboffins stunned by biggest brown dwarf at edge of our galaxyNot big enough to be a proper star; not small enough to be a planet Pic Astronomers claim to have identified the largest and purest brown dwarf – measuring in at a record-breaking 90 times the mass of Jupiter – hovering around the edges of the Milky Way.…Katyanna Quach
- WiGig spec hints at 5G offering 8 Gbps over 10 metresWi-Fi Alliance certification program kicks off with Intel, Qualcomm chipsets to the fore The WiFi Alliance reckons it's bestowed on a waiting world the first hint of what 5G will look like, and apparently it's a radio link that can manage 8 Gbps over 10 metres.…Richard Chirgwin
- Verizon boss: Yahoo! email hack 'is a big deal to us', we'll decide new price next monthLowell McAdam talks acquisitions, 5G and IoT Verizon is going to decide how much it is willing to pay for Yahoo! next month when an investigation into its massive security breach is completed.…Kieren McCarthy