EasyMorph for backup of PostgreSQL

DoctorYSG · January 28, 2020, 8:54pm

I was going to ask you whether you thought that using EasyMorph to backup a 1GB pg database made sense, or if it was better to use pg_dump -Fc (binary custom).

But after a few tests, I think the second makes more sense.

I am using your PowerShell Transform, but it reports an error at the end of the script. Funny, when I run this it creates the outfile just fine. And there are no errors when I run this from the local powershell shell.

Here is the output:(the only error is the last line).

Errors
pg_dump: last built-in OID is 16383
pg_dump:
 reading extensions


pg_dump:
 identifying extension members

pg_dump:
 reading schemas

pg_dump:
 reading user-defined tables

pg_dump:
 reading user-defined functions

pg_dump:
 reading user-defined types

pg_dump:
 reading procedural languages

pg_dump:
 reading user-defined aggregate functions

pg_dump:
 reading user-defined operators

pg_dump:
 reading user-defined access methods

pg_dump:
 reading user-defined operator classes

pg_dump:
 reading user-defined operator families

pg_dump:
 reading user-defined text search parsers

pg_dump:
 reading user-defined text search templates

pg_dump:
 reading user-defined text search dictionaries

pg_dump:
 reading user-defined text search configurations

pg_dump:
 reading user-defined foreign-data wrappers

pg_dump:
 reading user-defined foreign servers

pg_dump:
 reading default privileges

pg_dump:
 reading user-defined collations

pg_dump:
 reading user-defined conversions

pg_dump:
 reading type casts

pg_dump:
 reading transforms

pg_dump:
 reading table inheritance information

pg_dump:
 reading event triggers

pg_dump:
 finding extension tables

pg_dump:
 finding inheritance relationships

pg_dump: 
reading column info for interesting tables


pg_dump:
 finding the columns and types of table "se3d.cells"

pg_dump:
 finding the columns and types of table "se3d.materials"

pg_dump:
 finding the columns and types of table "se3d.electrodes"

pg_dump:
 finding the columns and types of table "se3d.electrolytes"

pg_dump:
 finding the columns and types of table "se3d.testPlans"

pg_dump:
 finding the columns and types of table "se3d.testData"

pg_dump:
 finding the columns and types of table "se3d.cells2tags"

pg_dump:
 finding the columns and types of table "se3d.creatorList"

pg_dump:
 finding the columns and types of table "se3d.tags"

pg_dump:
 finding the columns and types of table "se3d.seperators"

pg_dump:
 finding the columns and types of table "se3d.materials2tags"

pg_dump:
 finding the columns and types of table "se3d.seperators2tags"

pg_dump:
 finding the columns and types of table "se3d.electrodes2tags"

pg_dump:
 finding the columns and types of table "se3d.electrolytes2tags"

pg_dump:
 finding the columns and types of table "se3d.m2elect2cell"

pg_dump:
 flagging inherited columns in subtables

pg_dump: 
reading indexes

pg_dump:
 reading indexes for table "se3d.cells"

pg_dump:
 reading indexes for table "se3d.materials"

pg_dump:
 reading indexes for table "se3d.electrodes"

pg_dump:
 reading indexes for table "se3d.electrolytes"

pg_dump:
 reading indexes for table "se3d.testPlans"

pg_dump:
 reading indexes for table "se3d.testData"

pg_dump:
 reading indexes for table "se3d.creatorList"

pg_dump:
 reading indexes for table "se3d.tags"

pg_dump:
 reading indexes for table "se3d.seperators"

pg_dump:
 flagging indexes in partitioned tables

pg_dump:
 reading extended statistics

pg_dump:
 reading constraints

pg_dump: 
reading triggers


pg_dump:
 reading rewrite rules

pg_dump:
 reading policies

pg_dump:
 reading row security enabled for table "se3d.cells"

pg_dump:
 reading policies for table "se3d.cells"

pg_dump:
 reading row security enabled for table "se3d.materials"

pg_dump: 
reading policies for table "se3d.materials"

pg_dump:
 reading row security enabled for table "se3d.electrodes"

pg_dump:
 reading policies for table "se3d.electrodes"

pg_dump:
 reading row security enabled for table "se3d.electrolytes"

pg_dump:
 reading policies for table "se3d.electrolytes"

pg_dump:
 reading row security enabled for table "se3d.testPlans"

pg_dump:
 reading policies for table "se3d.testPlans"

pg_dump:
 reading row security enabled for table "se3d.testData"

pg_dump:
 reading policies for table "se3d.testData"

pg_dump:
 reading row security enabled for table "se3d.cells2tags"

pg_dump:
 
reading policies for table "se3d.cells2tags"

pg_dump:
 reading row security enabled for table "se3d.creatorList"

pg_dump: 
reading policies for table "se3d.creatorList"

pg_dump:
 reading row security enabled for table "se3d.tags"

pg_dump:
 
reading policies for table "se3d.tags"

pg_dump:
 reading row security enabled for table "se3d.seperators"

pg_dump:
 reading policies for table "se3d.seperators"

pg_dump:
 reading row security enabled for table "se3d.materials2tags"

pg_dump:
 
reading policies for table "se3d.materials2tags"

pg_dump:
 reading row security enabled for table "se3d.seperators2tags"

pg_dump:
 
reading policies for table "se3d.seperators2tags"

pg_dump:
 reading row security enabled for table "se3d.electrodes2tags"

pg_dump:
 reading policies for table "se3d.electrodes2tags"

pg_dump:
 reading row security enabled for table "se3d.electrolytes2tags"

pg_dump:
 reading policies for table "se3d.electrolytes2tags"

pg_dump:
 reading row security enabled for table "se3d.m2elect2cell"

pg_dump: reading policies for table "se3d.m2elect2cell"


pg_dump:
 reading publications

pg_dump:
 reading publication membership

pg_dump: reading publication membership for table "se3d.cells"

pg_dump:
 reading publication membership for table "se3d.materials"

pg_dump:
 reading publication membership for table "se3d.electrodes"

pg_dump:
 reading publication membership for table "se3d.electrolytes"

pg_dump:
 reading publication membership for table "se3d.testPlans"

pg_dump:
 reading publication membership for table "se3d.testData"

pg_dump:
 reading publication membership for table "se3d.cells2tags"

pg_dump:
 reading publication membership for table "se3d.creatorList"

pg_dump:
 reading publication membership for table "se3d.tags"

pg_dump:
 reading publication membership for table "se3d.seperators"

pg_dump:
 reading publication membership for table "se3d.materials2tags"

pg_dump:
 reading publication membership for table "se3d.seperators2tags"

pg_dump:
 reading publication membership for table "se3d.electrodes2tags"

pg_dump:
 reading publication membership for table "se3d.electrolytes2tags"

pg_dump:
 reading subscriptions

pg_dump:
 reading large objects

pg_dump:
 reading dependency data

pg_dump:
 saving encoding = UTF8
pg_dump:
 saving standard_conforming_strings = on

pg_dump:
 saving search_path = 

pg_dump: 
saving database definition

pg_dump:
 dumping contents of table "se3d.cells"

pg_dump:
 dumping contents of table "se3d.cells2tags"

pg_dump:
 dumping contents of table "se3d.creatorList"

pg_dump:
 dumping contents of table "se3d.electrodes"

pg_dump:
 dumping contents of table "se3d.electrodes2tags"

pg_dump:
 dumping contents of table "se3d.electrolytes"

pg_dump:
 dumping contents of table "se3d.electrolytes2tags"

pg_dump:
 dumping contents of table "se3d.materials"

pg_dump:
 dumping contents of table "se3d.materials2tags"

pg_dump:
 dumping contents of table "se3d.seperators"

pg_dump:
 dumping contents of table "se3d.seperators2tags"

pg_dump:
 dumping contents of table "se3d.tags"

pg_dump:
 dumping contents of table "se3d.testData"

pg_dump:
 dumping contents of table "se3d.testPlans"

Execution failed.

The script looks like

$pg = "D:\Program Files\PostgreSQL\12\bin\pg_dump"

$outDir = "\\fs5-projects\SE3D\Database\Backups\"

$hostName = "vwdse3d.com"

$userName = "postgres"

$dayString = get-date -format "M-d-yyyy"

$file = $dayString + ".se3d.pgdump"

$path = $outDir + $file

$env:PGPASSWORD = "<none of your business>"

<# -Fc=custom binary format, -v=verbose, -Z9=highest compression #>

&$pg -h $hostName -p 5432 -U $username -f $path -Fc -v -Z9 postgres

dgudkov · January 28, 2020, 9:14pm

Using pg_dump would definitely make more sense since it’s a purpose-built utility for the case.

Did you try running the script in the PowerShell console, not in EasyMorph? Did it produce the same error?

DoctorYSG · January 28, 2020, 11:14pm

I did run the script in the shell. No error printed. I did state that in the initial post, but it probably was not clear.

Do you think the pg-dump has some sort of hidden status code?

andrew.rybka · January 29, 2020, 11:17am

pg_dump docs state that verbose mode (the -v option) will cause pg_dump to output progress messages to the standard error stream (STDERR).

And the “PowerShell” will show “Execution failed” message when some errors were added to the STDERR during script execution but there was no actual exception.

You should either remove the -v option or ignore the “Execution failed” message and find some other way to check if a dump was successful. For example, you can check $LastExitCode or $? variables.