1

I have a PostgreSQL v7.3 installation that I need to migrate with a minimum of fuss to a newer, resource-rich and much faster machine that already has PostgreSQL 9.1.13 installed. I have the source code to the version of v7.3 that I was using, which, if compiled and installed (easy enough) would make it dead-simple (assuming prying 9.1.13 out of there first isn't a problem), but I'd prefer to use the newer version if possible.

I have the v7.3 pgsql directory on the new machine as a copy from the older machine, not presently associated with the PostgreSQL on this one.

There are a lot of databases in the datasets, some of them archival in the sense that they've not been used in years, but I would very much like to retain them.

None of them use triggers; they're pretty basic. Tables with columns, a few indexes each.

I know you can output one DB to SQL and then feed it to the new installation, but that feels very formidable, detail-intensive and time-consuming to me.

Is there a one-shot way to get this done?

Improve this question
5
  • I would give pg_dump / pg_restore a try before you go looking for alternatives. They do essentially "output one DB to SQL and then feed it to the new installation," but generally aren't "very formidable, detail-intensive and time-consuming". Commented Jun 9, 2015 at 15:18
  • Ugh. They are very much so when you have a large number of databases. That process is specifically what I'm interested in avoiding. It's absurdly granular for what I'm trying to accomplish. I may end up going there before this is over if it is the case that there is no actual installation-level migration process, but it seems more than a little surprising that would be so. Commented Jun 9, 2015 at 15:24
  • 1
    pg_upgrade could help if you were on >=8.3. Since you're not, pg_dumpall? Commented Jun 9, 2015 at 15:28
  • pg_dump as a SQL script plus psql is not in any way something to rule out. You can actually pipe the output of pg_dump to psql so no intermediate files are necessary. This can be automated using a shell script. Given the ancient source version I doubt that there is any other way of doing that - you most definitely can not use the existing data directory for anything else than 7.3 Commented Jun 9, 2015 at 15:54
  • Jay, pg_dumpall was exactly what I was looking for. All databases migrated successfully, two trivial steps. Awesome. Make it an answer instead of a comment, and I'll vote it up. If not, well, I voted your comment up. :) Commented Jun 9, 2015 at 23:03

1 Answer 1

Reset to default
1

pg_dump is the normal answer for a single database, pg_upgrade does an entire cluster on the same machine, but only works with >=8.3. Failing all of that, pg_dumpall will pg_dump all the databases on a server into a single output stream.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

...at which point, to recreate, you log in as the DB user and do this: psql -f myBackup.sql template1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.