Productive Linux
ACPI administration advocacy advocacy advocacy opinion alsa amarok apache apple apt aptitude archive audio audo authentication automount avi awk backup bash BIOS boot browser business bzip cache calendar calibre cdr cdrecord censorship commandline computerscience console convert cron cut database date debian degree design desktop development disk dpkg dvd economics education emacs email europe exim faad ffmpeg file files firefox firewall flash foss freedom ftp fun fuse git gnumeric graphics grep growisofs grub gtkpod gzip hardware hardware html icedove idiocy image imagemagick images installation ip iphone ipod iptables iso itunes ivman kde kernel keyboard knoppix lame laptop latex libreoffice linux locale lockin locking longlines lsof m4a microsoft mimetypes minitab mogrify mount mp3 mp4 mplayer multimedia music mysql network nfs nfs4 nmap openbox openfiles openoffice opinion opinion orgmode partition pdf pdftk perl php podcast politics pomodoro ports postgresql print printing privacy process programming rant remote rhythmbox rss rsync rxvt scp screengrab screenshot script scripting scsi security sed server services shell siteadmin sitenews sitesoftware skype skype slackware sound sox spam spreadsheet ssh statistics subversion sudo svk swap t23 t43 tar terminal tex text thinkpad thunderbird time timer timezone ubuntu udev upgrade usb usbmount users uuid versioncontrol vfat video vnc windows wine wordpress wordprocessing X40 xwindows xwindows youtube
I had some data in a postgres database on a laptop that died. Stupidly I didn't back up that data. However, using some hacker-jitsu (i.e. buying a hard drive-to-USB coverter) I had access to the data. However, there were further problems.
The documentation says that postgresql data files are not compatible from each major version to another. So 7.4 data are not compatible with 7.5 data let alone 8.2. Hence forcing 8.2 to read the 7.4 data files was bound to fail. In any case, the command line utiltites 'pg_ctl' and 'pg_ctlcluster' did not seem to respond to the -D switch which set the data directory.
To move data from one postgresql version to another the documentation advises to use postgres's pg_dump and pg_restore utilities. Alternatively there is the pg_dumpall utility.
There also seems is a 'pg_upgrade' utility, but I couldn't get my head around the documentation.
So, I mish-mashed all this information in my head and came up with a strategy.
The first thing I did was to install the old postgresql:
apt-get install postgresql-7.4
I then stopped the postgresqls from running.
/etc/init.d/postgresql-8.2 stop
/etc/init.d/postgresql-7.4 stop
I copied the data from the usb drive to the current drive. I guessed (correctly as it turned out) that, on a Debian system, the postgres data are in /var/lib/postgresql/7.4/. Your system might be different.
rsync -avz /media/disk-1/lib/postgresql/7.4/ /var/lib/postgresql/7.4/
Made sure that the owners are right:
chown -R postgres.postgres /var/lib/postgresql/7.4/
Now, time to get the database data. Note the use of the 'su' command as I can't be arsed to struggle with postgresql's authentication system, especially for one operation.
Start the pg server:
/etc/init.d/postgresql-7.4 start
Get a dump of the old data:
su -c 'pg_dumpall > outfile.sql' postgres
Stop the old server and start the new one:
/etc/init.d/postgresql-7.4 stop /etc/init.d/postgresql-8.2 start
Finally, load the data into the new server:
su -c 'psql -f outfile.sql' postgres
And there you have it.
Stumble It!
Digg it
Reddit
