Thread: psql terminates on first error
Hi everyone, While trying to transfer data from one instance to another using pg_dumpall and psql, the psql stopped on first error, causing pg_dumpall to terminate as well. This behaviour was seen on Windows (EDB installer), but is different on Linux. Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of course to continue reading) psql continued after first error. Not even the option -v ON_ERROR_STOP=0 helped. Why is the behaviour between Linux and Windows different? This is not reflected in the documentation. Kind Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment
> On 02/03/2023 12:53 CET Holger Jakobs <holger@jakobs.com> wrote: > > While trying to transfer data from one instance to another using > pg_dumpall and psql, the psql stopped on first error, causing pg_dumpall > to terminate as well. > > This behaviour was seen on Windows (EDB installer), but is different on > Linux. Database on Windows and client on Linux? What's the error and is it the same on Linux? Is there even an error on Linux? > Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of > course to continue reading) psql continued after first error. Do you use PowerShell and pipe pg_dumpall into psql? The first thing that comes to my mind when reading Postgres+Windows is that PowerShell up to version 5.1 converts stdout to UTF-16LE. This already tripped me up in past with pg_dump and psql. -- Erik
Am 02.03.23 um 13:32 schrieb Erik Wienhold: >> On 02/03/2023 12:53 CET Holger Jakobs <holger@jakobs.com> wrote: >> >> While trying to transfer data from one instance to another using >> pg_dumpall and psql, the psql stopped on first error, causing pg_dumpall >> to terminate as well. >> >> This behaviour was seen on Windows (EDB installer), but is different on >> Linux. > Database on Windows and client on Linux? What's the error and is it the same > on Linux? Is there even an error on Linux? Both on Windows or both on Linux >> Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of >> course to continue reading) psql continued after first error. > Do you use PowerShell and pipe pg_dumpall into psql? The first thing that > comes to my mind when reading Postgres+Windows is that PowerShell up to > version 5.1 converts stdout to UTF-16LE. This already tripped me up in > past with pg_dump and psql. No, it was in cmd.exe or in bash > -- > Erik -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment
Holger Jakobs schrieb am 02.03.2023 um 13:44: >>> Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of >>> course to continue reading) psql continued after first error. >> Do you use PowerShell and pipe pg_dumpall into psql? The first thing that >> comes to my mind when reading Postgres+Windows is that PowerShell up to >> version 5.1 converts stdout to UTF-16LE. This already tripped me up in >> past with pg_dump and psql. > No, it was in cmd.exe or in bash I find using pipes with the Postgres command line tools on Windows unreliable in general (including cmd.exe). So if you did use redirection/pips, try without.
Am 02.03.23 um 13:48 schrieb Thomas Kellerer: > Holger Jakobs schrieb am 02.03.2023 um 13:44: >>>> Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of >>>> course to continue reading) psql continued after first error. >>> Do you use PowerShell and pipe pg_dumpall into psql? The first thing that >>> comes to my mind when reading Postgres+Windows is that PowerShell up to >>> version 5.1 converts stdout to UTF-16LE. This already tripped me up in >>> past with pg_dump and psql. >> No, it was in cmd.exe or in bash > I find using pipes with the Postgres command line tools on Windows unreliable > in general (including cmd.exe). So if you did use redirection/pips, try without. > > Whether I use pipes or writing stdout of pg_dumpall to a file and reading back with psql from the file made no difference. -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment
> On 02/03/2023 14:30 CET Holger Jakobs <holger@jakobs.com> wrote: > > Am 02.03.23 um 13:48 schrieb Thomas Kellerer: > > Holger Jakobs schrieb am 02.03.2023 um 13:44: > >>>> Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of > >>>> course to continue reading) psql continued after first error. > >>> Do you use PowerShell and pipe pg_dumpall into psql? The first thing > >>> that comes to my mind when reading Postgres+Windows is that PowerShell > >>> up to version 5.1 converts stdout to UTF-16LE. This already tripped me > >>> up in past with pg_dump and psql. > >> No, it was in cmd.exe or in bash > > I find using pipes with the Postgres command line tools on Windows > > unreliable in general (including cmd.exe). So if you did use > > redirection/pips, try without. > > Whether I use pipes or writing stdout of pg_dumpall to a file and > reading back with psql from the file made no difference. Try pg_dumpall with option -f instead of redirecting stdout: pg_dumpall -f dump.sql psql -f dump.sql Redirecting stdout to an intermediate file should have the same effect as piping to psql. And what error do you get from psql? -- Erik
Holger Jakobs <holger@jakobs.com> writes: > While trying to transfer data from one instance to another using > pg_dumpall and psql, the psql stopped on first error, causing pg_dumpall > to terminate as well. > This behaviour was seen on Windows (EDB installer), but is different on > Linux. > Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of > course to continue reading) psql continued after first error. > Not even the option -v ON_ERROR_STOP=0 helped. > Why is the behaviour between Linux and Windows different? This is not > reflected in the documentation. It shouldn't be different. However, this report would be trivially explained if your Windows machine has an %APPDATA%\postgresql\psqlrc.conf file that turns ON_ERROR_STOP on. You could try invoking psql with the -X command line switch to prevent reading that. regards, tom lane
Am 02.03.23 um 15:33 schrieb Tom Lane: > Holger Jakobs <holger@jakobs.com> writes: >> While trying to transfer data from one instance to another using >> pg_dumpall and psql, the psql stopped on first error, causing pg_dumpall >> to terminate as well. >> This behaviour was seen on Windows (EDB installer), but is different on >> Linux. >> Only when using an option -c "\set ON_ERROR_STOP false" (plus -f - of >> course to continue reading) psql continued after first error. >> Not even the option -v ON_ERROR_STOP=0 helped. >> Why is the behaviour between Linux and Windows different? This is not >> reflected in the documentation. > It shouldn't be different. However, this report would be trivially > explained if your Windows machine has an %APPDATA%\postgresql\psqlrc.conf > file that turns ON_ERROR_STOP on. You could try invoking psql with > the -X command line switch to prevent reading that. > > regards, tom lane BINGO! Thanks a lot. Sometimes you forget what you have done earlier. ;-) -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012