Thread

1. two instances of postgres on the same machine?

   Martin Mueller <martinmueller@northwestern.edu> — 2018-07-15T19:40:52Z

   I made a stupid mistake with a backup and would be grateful for some advice on how to extricate myself.
   
   I created a dumpfile from a 10.3 postgres installation on a Mac and tried to restore it on another Mac 1,000 miles away, but forgot that that machine runs Postgres 9.6.
   
   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,
           ^
   ERROR:  syntax error at or near ""
   LINE 1:     xmlid character varying(25),
           ^
   ERROR:  syntax error at or near ""
   LINE 1:     wordtuple character varying(300) NOT NULL,
           ^
   ERROR:  syntax error at or near ""
   LINE 1:  SELECT eebochrontuples.spelling,
           ^
   ERROR:  syntax error at or near ""
   LINE 1:     tuple character varying(150) NOT NULL,
           ^
   ERROR:  syntax error at or near ""
   LINE 1:     xmlid character varying(25),
           ^
   ERROR:  syntax error at or near ""
   LINE 1:     AS integer
   
       ...
   
   invalid command \N {3,000 occurrences}
   
   
   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.  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?
   
   Another solution would be to upgrade the current 9.6 installation. But I could at a pinch work with the older data on that database, and since I don’t really know what I’m doing when it comes to server administration, I’m very reluctant to touch what is working.
   
   With apologies for this very primitive question and thanks in advance for any advice
   
   
   Martin Mueller
   Professor emeritus of English and Classics
   Northwestern University
   
   
   
   

2. Re: two instances of postgres on the same machine?

   Tom Lane <tgl@sss.pgh.pa.us> — 2018-07-15T20:16:46Z

   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 miles away, 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
   
   
   

3. Re: two instances of postgres on the same machine?

   Martin Mueller <martinmueller@northwestern.edu> — 2018-07-15T22:12:15Z

   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 miles away, 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