Many thanks for your generous help, and on a Sunday afternoon as well.
This is a wonderful listserv, and I am deeply indebted to it.
MM
On 7/15/18, 2:17 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Martin Mueller <martinmueller@northwestern.edu> writes:
> I created a dumpfile from a 10.3 postgres installation on a Mac and tried to restore it on another Mac 1,000
milesaway, but forgot that that machine runs Postgres 9.6.
OK ...
> The command
> /Applications/Postgres.app/Contents/Versions/9.6/bin/psql -U postgres earlyprint2 </volumes/"theseus
1"/earlyprint_backup.sq.tar
> generated a long error list like this:
> ERROR: syntax error at or near "toc"
> LINE 1: toc.dat spelling character varying(150) NOT NULL,
> ^
I gather from the ".tar" filename that you used -Ft dump format; if so,
you need to feed it to pg_restore not plain psql. The errors you're
showing here look somewhat consistent with the theory that you got that
wrong, although other explanations are certainly possible.
> I can’t tell from the error list whether it’s a matter of a corrupted
> backup file or (more probably) a result of 9.6 not reading a 10.3 dump
> file.
While the latter is possible, I think that typically it would only happen
if the 10.x installation is using SQL DDL features that 9.6.x doesn't
have. I would try doing what you're doing, but with the right process...
> If the latter, what would be the safest way of cleaning up this mess?
> There is a side of me that would prefer installing a 10.3 on the same
> machine in addition, but I’m not sure whether I can do this in the
> environment of the Postgres App. I access the data via Aqua Studio that
> looks for the server on port 5432, which seems to be a standard port.
> Is there a safe way of installing a second server via a different port?
You can certainly install multiple PG servers on one machine; most of the
developers have several different PG versions laying about. What you need
for that is separate install directories, separate data directories, and
separate port numbers for each server. This is easy to achieve if
building from source, but may be nigh impossible if you are installing
somebody else's packaging and they didn't make provisions for it. I don't
know much about the Postgres App packaging so I can't offer any advice
there. I also don't know how hard it is to get the Aqua client you
mention to talk to a nonstandard port number, though I'd guess that it's
possible.
regards, tom lane