PostgreSQL Windows – How to Save psql ERROR Output to Same File as DDL Commands

postgresqlwindows

I'm trying to restore a database from a text file. I'm following the answer on this question. How do I restore a plain text postgres .backup file

But add: > c:\result.txt

psql.exe -U  sde sde < c:\backup\sde_04_06_2018.backup > c:\backup\result.txt

This seem to work but doesn't write the errors.

You get messages like this on the text file:

REVOKE
GRANT
SET
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES

But on the command windows you see error like this:

ERROR:  constraint "sde_layer_stats_version_id_fkey" for relation sde_layer_stats" already exists
ERROR:  constraint "xml_columns_fk1" for relation "sde_xml_columns" already exists
ERROR:  constraint "xml_columns_fk2" for relation "sde_xml_columns" already exists
ERROR:  constraint "xml_indextags_fk1" for relation "sde_xml_index_tags" already exists

The idea is save the error on the text file so I can review and decide if are something I need to handle.

Best Answer

I want help with how write the command line so I can save the output of the restore to a file.txt. So the create/insert/alter on the backup aren't relevant. And the image is just as reference, I also include the message as text just in case.

If you're asking how do you put the error to the "result file" too, you need to redirect STDERR as well as STDOUT or direct STDERR to STDOUT with 2>&1

psql.exe -U  sde sde < c:\backup\sde_04_06_2018.backup > c:\backup\result.txt 2>&1

Internally you have three "standard" streams: input, output, and error.

input you're directing that to c:\backup\sde_04_06_2018.backup
output you're directing output to c:\backup\result.txt
You need to also redirect error.

Related Solutions

Postgresql – check key if exists in other table without fk constraint

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

PostgreSQL – How to Perform Dump and Restore

Apparently the problem was the > operator. Changing that to -f to specify the file for the dump, resolved the problem (using the plain format).

I was writing a Powershell script which may have been the underlying cause. The commands were accepted, but there could have been a corruption issue there.

Best Answer

Related Solutions

Postgresql – check key if exists in other table without fk constraint

PostgreSQL – How to Perform Dump and Restore

Related Question