0

I want to move a database from one machine to another but I get an error. The source has a slightly older version of Postgres. I started out using PGAdmin (because I thought that what I wanted to do was as common as breathing) and dropped back to using command line because "Failed (exit code:1)" that PGAdmin 4 was giving me was not helpful.

So from the source computer:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Program Files\PostgreSQL\10\bin>pg_dump --version
pg_dump (PostgreSQL) 10.7

C:\Program Files (x86)\pgAdmin 4\v4\runtime\pg_dump.exe --file "C:\\PGData\\WellData.tar" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=t --blobs "WellData"

And on the destination computer

C:\Program Files\PostgreSQL\10\bin>pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "WellData" --section=data --verbose "E:\\PGData\\WellData.tar"
pg_restore: [archiver] unsupported version (1.14) in file header

C:\Program Files\PostgreSQL\10\bin>pg_restore --version
pg_restore (PostgreSQL) 10.8

I would rather understand the problem and fix it than work around it, but I did try backing up the data as SQL. When I found that running the restore SQL statement would not work because it did not like the \N values that were used to indicate null values I gave up. (the backup uses notation that the restore cannot handle? That doesn't seem right)

WeststarEric
  • 321
  • 1
  • 2
  • 12
  • What was the complete `pg_restore` command you gave that caused the error? Does '...backing up the data as SQL ' mean as plaint text? If so what was the restore procedure you used in that case, hint you can't use `pg_restore`. Add answers as update to your question. – Adrian Klaver Jan 07 '22 at 22:01
  • Sorry. I did not start my inserted code on a new line, so it did not appear. Restore command now shows up. – WeststarEric Jan 10 '22 at 16:03
  • 1) The dump was done using `C:\Program Files (x86)\pgAdmin 4\v4\runtime\pg_dump.exe` not the `pg_dump` from the `\bin` directory. I would try doing the dump using the `\bin` version. 2) You have not explained '...backing up the data as SQL '. If this was indeed as plain text, then how did you try to restore it? – Adrian Klaver Jan 10 '22 at 16:14
  • The Plain Text backup option generates SQL. When I used that backup option, I ran that SQL to restore it. My description was an attempt to balance clarity against explaining what is already familiar to those that understand the process. – WeststarEric Jan 10 '22 at 16:16
  • But you said that restoring the plain text version did not work: 'When I found that running the restore SQL statement would not work because it did not like the \N values that were used to indicate null values I gave up.' – Adrian Klaver Jan 10 '22 at 16:23
  • I found the answer to my SQL problem. I was using PGAdmin which creates a COPY FROM stdin statement and appends the table being exported. Thanks to this question and answer https://stackoverflow.com/questions/32271378/in-postgresql-how-to-insert-data-with-copy-command I now know that PGAdmin can't handle that. Copying and pasting the data into a text file (excluding the end of data marker) and then running a copy from statement that references that text file works just fine. That still leaves the original question of why PGAdmin is not backing up a database to and from a TAR file. – WeststarEric Jan 10 '22 at 22:16
  • Not sure what that link is supposed to be showing me? If you mean to point out that the `pgAdmin` query editor is broken then yes that is the case, but `pgAdmin` is not Postgres it is just a client. If you want to restore the plain text use `psql`. – Adrian Klaver Jan 10 '22 at 22:17
  • You have to quit thinking of `pgAdmin` as Postgres, it is not it is just a rather clunky client. The `COPY FROM stdin` is coming from `pg_dump`. Again `pgAdmin` is not creating the tar file `pg_dump` is. Also again try using `pg_dump` from the 'bin\' directory not the 'runtime\' directory. – Adrian Klaver Jan 10 '22 at 22:22

0 Answers0