topic PostgreSQL - Important Backup Info in ArcGIS Enterprise Questions https://community.esri.com/t5/arcgis-enterprise-questions/postgresql-important-backup-info/m-p/259769#M10036 <HTML><HEAD></HEAD><BODY><P>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. <STRONG>THIS IS NOT ADVISED</STRONG>. </P><P></P><P>Before you read anything else, switch your pg_dump command right now to use either "custom" or "tar" backup formats, see the doc here: <A class="link-titled" href="https://www.postgresql.org/docs/9.6/app-pgdump.html" title="https://www.postgresql.org/docs/9.6/app-pgdump.html">PostgreSQL: Documentation: 9.6: pg_dump</A>&nbsp;</P><P></P><P>Why is this so important? Because when using ST_Geometry spatial type, certain schemas have to be restored <STRONG>before</STRONG> 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: <A class="link-titled" href="http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/restore-geodatabase-postgresql.htm" title="http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/restore-geodatabase-postgresql.htm">Restore a geodatabase to PostgreSQL—Help | ArcGIS Desktop</A>. Restoring the entire .sql file resulted in tons of errors and ultimately a blank geodatabase. Schema seemed OK, but data was missing.</P><P></P><P>Everything in the above ESRI doc says to use pg_restore, but that cmd only takes "custom" or "tar" backup files as input, <STRONG>NOT</STRONG> plain text .sql.</P><P></P><P>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.</P></BODY></HTML> Wed, 06 Feb 2019 14:41:07 GMT danbecker 2019-02-06T14:41:07Z PostgreSQL - Important Backup Info https://community.esri.com/t5/arcgis-enterprise-questions/postgresql-important-backup-info/m-p/259769#M10036 <HTML><HEAD></HEAD><BODY><P>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. <STRONG>THIS IS NOT ADVISED</STRONG>. </P><P></P><P>Before you read anything else, switch your pg_dump command right now to use either "custom" or "tar" backup formats, see the doc here: <A class="link-titled" href="https://www.postgresql.org/docs/9.6/app-pgdump.html" title="https://www.postgresql.org/docs/9.6/app-pgdump.html">PostgreSQL: Documentation: 9.6: pg_dump</A>&nbsp;</P><P></P><P>Why is this so important? Because when using ST_Geometry spatial type, certain schemas have to be restored <STRONG>before</STRONG> 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: <A class="link-titled" href="http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/restore-geodatabase-postgresql.htm" title="http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/restore-geodatabase-postgresql.htm">Restore a geodatabase to PostgreSQL—Help | ArcGIS Desktop</A>. Restoring the entire .sql file resulted in tons of errors and ultimately a blank geodatabase. Schema seemed OK, but data was missing.</P><P></P><P>Everything in the above ESRI doc says to use pg_restore, but that cmd only takes "custom" or "tar" backup files as input, <STRONG>NOT</STRONG> plain text .sql.</P><P></P><P>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.</P></BODY></HTML> Wed, 06 Feb 2019 14:41:07 GMT https://community.esri.com/t5/arcgis-enterprise-questions/postgresql-important-backup-info/m-p/259769#M10036 danbecker 2019-02-06T14:41:07Z