updating postgres

rhaggert

what things do i need to be aware of or do to get an older version of postgres on the "old machine" to put in a the "new machine" running a new version of postgres

i want to keep all the users and thier databases.

thanks for any help

Sxooter

You'll need to use pg_dumpall on the source machine. The new machine should be able to then read the dump file with either 'pg_restore dumpfile' or 'psql template1 <dumpfile'

Note that you'll need to have all the user accounts in unixland first if you want pgsql to suck in their ids. also, there may be some issues with passwords if you've got the new one set

If you want 7.3. style dependencies added, look in the /contrib directory for the adddepend code.

rhaggert

well it is a unix machine
distro red hat 8

pg 7.3.2

tried doing a pg_dumpall

now should everything be handled by
"
pg_restore dumpfile' or 'psql template1 <dumpfile
"

the accounts are all there, if u mean as in on they system, yes and if you mean as in postgres users, yes

im not the sys admin but i am working on it with him since i am one of the more heavier pg users and we need to get every thing ported over to the new system

thanks for the help

rhaggert

pg 7.1 on rh 7.3
going to
pg 7.3.1 on rh 8.0

7.1 doesnt seem to have the same options (for pg_dumpall and pg_restore) as 7.3 does, or at least in the doc
im talking about the -F option wich seems important from what i got out of man pages

many errors are given about
WARRING: blah blah tmp_shadow blah: Invalid

thats the gist of it

one other thing. is it easy to just run somthing or some sql as postgres usper user to delete all users in case the db was messed up and you want to start over with something clean

thanks for any help

Sxooter

When I migrated from 7.2 to 7.3, I used pg_dump on the 7.3 machine against the 7.2 machine like so:

7.3machine>$ createdb dbname
7.3machine>$ pg_dump -h 7.2machine dbname | psql dbname

for each database by hand.

rhaggert

thats what i was kind of concluding to from what I have gatherd online and the man pages.

but isn't there some issues with globals that pg needs to be present in the "new" dmbs

also, would it be a good idea just to get a list of the users of the old system and run a loop of

loop(list of users)
pg_dump -h 7.2machine user-db | psql user_db

and, is there a diffrence in these comands

loop(list of users)
pg_dump -h 7.2machine user-db | psql user_db

and

loop(list of users)
pg_dump -Fh 7.2machine user-db > pg_restore -'the options'

again, thanks

Sxooter

No, pg_restore and psql are pretty similar, it's just that there are certain formats of pg_dump that pg_restore will choke on but psql will work on.

I never actually use pg_restore myself, as psql seems to always work, and pg_restore is one more program to keep track of in my brain. :-)

csn

I just did pg_dump_all / psql -f ... when upgrading from 7.2 to 7.3.

rhaggert

hey thanks, I'm going to pass this idea on to the sys admin

there was a concern about globals that pg keeps track of, of course i know nothing of them. would

new_machine> pg_dump -h old_machine dbname | psql dbname

miss any 'globals' that pg 'needs' in able to migrate from the old to the new.

one other question, i was reading at postgres forums about 7.2 7.3 migrate with large objects like blobs, some one on the system uses blobs to store stuff like jpegs, so this is a concern. no real clear answer was given to the question that I am talking about, do you have any thoughts on this? a pg migrate from 7.1 to 7.3 with large objects.

thanks