Migrating database from one profile to another

When I signed up, I selected a 64bit OS. Since then, I have learned that a 32bit OS would be better for me since I'm on the 360 plan and RAM runs tight.

I resized my 64bit partition, and with the free space, created a new partition with Ubuntu 9.10 32bit installed. I then mounted the 32bit disk image to my 64bit machine, and copied over files. All thats left now is my postgres and mysql databases. Whats the easiest way to copy these over? I already tried "pg_dumpall > backup" but when I tried to "psql < backup" it brought up a ton of errors. My postgres database uses PostGIS, which I think is messing things up.

Is there any way to copy a folder over or something which will directly transfer over everything (such as roles and database)?

2 Replies

Your data is stored in /var/lib/postgresql but I'm not sure if those raw files would be compatible across the 32-bit/64-bit divide. Try copying that folder over to the new disk image and see if pgsql recognizes them.

If that doesn't work, your only choice is to dump & reload. Better get used to it, b/c you might have to do it again when you upgrade. Especially with Ubuntu which tends to break things upon upgrade.

Before reloading the data, make sure to install all pgsql modules you've been using in the old setup. Then recreate the roles. If you have a lot of complicated roles, it might take less time to write a script to do it. Finally, reload the data for each database.

@nbv4:

All thats left now is my postgres and mysql databases. Whats the easiest way to copy these over? I already tried "pgdumpall > backup" but when I tried to "psql < backup" it brought up a ton of errors. My postgres database uses PostGIS, which I think is messing things up. At least for PostgreSQL, I think the dump/reload is the best bet. pgdumpall is in fact the right way to dump an entire cluster (e.g., all databases, roles, and access rights). One important thing to remember is that when you try to reload it into the database you will have to connect as a superuser (by default postgres) in order to be able to execute the various create operations.

An alternate approach is to use pgdumpall just to dump the roles (-r) and then use pgdump to individually dump any databases. You can then install the roles (with psql) and restore each database in turn (with psql or pgrestore) which can give you a little more control if desired. Also, for larger databases, the custom format for pgdump (-Fc) is compressed so can take up much less space.

You will want to make sure that any modules (languages, etc..) you have installed in the old cluster are also installed in the new cluster.

Can you summarize any of the more frequent errors? It might help indicate which piece is missing.

I might also suggest (to help with the test process) - leave your current Linode operational as 64-bit. Create a brand new Linode temporarily (you'll only get billed for the days you use it) as 32-bit. Then you can have both databases live at the same time and make testing methods of transferring the data easier (e.g., pg_dumpall -h xxxx | psql).

Once everything is done and you're happy with it, clone your new disk images back to your old Linode and delete the new Linode.

– David

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct