PostgreSQL 9.5 installed on Linux Ubuntu 16.04, using ST_Geometry spatial type. I had a cron job scheduled to backup all databases nightly using pg_dump cmd, backup output was set to plain-text SQL (.sql) file. THIS IS NOT ADVISED.
Before you read anything else, switch your pg_dump command right now to use either "custom" or "tar" backup formats, see the doc here: PostgreSQL: Documentation: 9.6: pg_dump
Why is this so important? Because when using ST_Geometry spatial type, certain schemas have to be restored before others. When you have a plain text .sql dump as I did, you are unable to hand-pick what schemas are restored, it's an "all or nothing" restore using psql. Take a look at the ESRI postgre restore doc here: Restore a geodatabase to PostgreSQL—Help | ArcGIS Desktop. Restoring the entire .sql file resulted in tons of errors and ultimately a blank geodatabase. Schema seemed OK, but data was missing.
Everything in the above ESRI doc says to use pg_restore, but that cmd only takes "custom" or "tar" backup files as input, NOT plain text .sql.
I ended up getting everything restored by searching for "schema: public" in the .sql dump, then running those sql queries first, then restoring the entire .sql dump with psql. Painful lesson, be advised.
