From: | Sai Hertz And Control Systems <sank89(at)sancharnet(dot)in> |
---|---|
To: | Anjan Dave <adave(at)vantage(dot)com> |
Cc: | Postgres Admin List <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Upgrading from 7.2.4 (RH 8) to 7.4 (RH9) |
Date: | 2003-12-10 06:32:01 |
Message-ID: | 3FD6BDE1.7000802@sancharnet.in |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Dear Anjan Dave ,
> I would like to start planning on upgrading a few servers from RH8 to
> RH9, essentially, also upgrading Postgres from 7.3.2 to 7.4 from the OS.
>
> There's also a box with RH7.3 (postgres 7.2.1) that could be upgraded
> to RH9 and Postgres 7.4.
>
> I am not sure if I should backup (pg_dump) the databases, and do a
> clean install of the OS, and do a pg_restore...OR, just upgrade the OS..?
>
This is what I do when migrating .
1. Take a file system backup of PostgreSQL "data" folder preferably
with DUMP
2. Take a back up of data with
pg_dump --disable-triggers -U <USER NAME> -a -d -b -D -Fc -Z 9
-f filename.tar.gz <DATABASE NAME>
a. This will take care of blobs (Though 7.2 does not have it)
b. Make sure that your data be less than 8 GB as tar could not
handle more than that
3. Yes Schema is also required to be backed up with above command and
having switch as
pg_dump --disable-triggers -U <USER NAME> -s Fp -f
schema.sql <DATABASE NAME>
4. Now why Schema is dumped separately to that of data
Because :
a. pg_dump will dump the schema as per its own wish and not as per
the database requirement so
if a required user defined function is not in the secema dump as
early as it is required your pg_restore will
abort with Xmas bells
b. In this case you can rearrange the creation of Schema as
Create functions > then table > then sequences > then views
5. Now after all stuff you have done install your new OS
initdb a new database in the same directory location as it was in
the old one (i.e in the older OS)
6. Now try restore the Schema if succeeds then go to 7 else
rearrange your sql statements to satisfy the monster
7. Restore Data with
pg_restore --disable-triggers -U <USER NAME> -d <DATABASE NAME>
./filename.tar.gz
8. Now if 6 and 7 fails you have a chance reinstall the older version
of postgresql and also its DUMP file to regain the data and schema again
and start again from point 2 to 8
This is what I do to restore / migrate and have got 100 % results till date
Any one having a better and more reliable method please pass it on to
Anjan Dave and me
We would be grateful to you
Regrads,
V Kashyap
From | Date | Subject | |
---|---|---|---|
Next Message | Bhartendu Maheshwari | 2003-12-10 09:51:20 | Re: Running Postgres Daemons with same data files |
Previous Message | Ashok Chauhan | 2003-12-10 05:26:01 | error in code |