PostgreSQL Backup and Restore - abcsoftware/abc-docs GitHub Wiki

SQL Password

To enable running SQL backups without user interaction you'll need to enter the password in the file C:\Users\<user name>\AppData\Roaming\postgresql\pgpass.conf like this:

localhost:4012:*:postgres:<password>

If it still asks for password, verify spelling on postgresql and pgpass.conf. Check for a .txt file extension if you created the file with notepad.

Cluster Dump Before Backup

You can add the cluster dump command as a command to complete before a Syncback profile is run. This is a good way to push all of the legacy data files and the SQL backup file to a backup server.

Put the following command in the "Run Program before option" in the daily profile: "C:\ABC Software\ABC Postgresql 12\PostgreSQL\bin\pg_dumpall.exe" --clean --if-exists -U postgres -h localhost -p 4012 -f "C:\ABC Software\Server\SQL Backups\abc_for_backup_server.sql"

Since the plain text backups can be very large, you may not be able to execute the script from ABC Toolbox or pgadmin. You can execute the script via command line. How to restore.

1. If the database name already exists on the server, drop it, or rename it in pgadmin.
2. Open a command prompt in the Postgres/x.x/bin folder. It may be in your Program Files.
3. Use command line: psql -h <server name> -p <port> -U <username> -f <filename>
4. Example: psql -h localhost -p 4012 -U posgres -f \abcfile\abc_postgresql_backup.sql
5. Enter the password if prompted.

1. Install the same version ABC Server Core as the one on the main server.
2. Use the same password for the postgres user as the one on the main server.
3. Copy the legacy server files (except server.config and netw.exp) and the cluster dump with Syncback on the main server.
4. Add a desktop shortcut to the backup server to restore the cluster dump.
5. The legacy server folder must be the same location it is on the main server.
6. The server.config needs to have the backup server or localhost for webServerUri.
7. The server.config and netw.exp files must not be overwritten in the backup process.

A Powershell script for backing up the Postgresql database cluster is available. You can create a Windows Scheduled Task to run the script automatically each night.

You may need to enable Powershell script execution in Windows. See Google or here:

https://windowsloop.com/enable-powershell-scripts-execution-windows-10/

Note: The compress-archive part of the script has a 2GB limit. The Syncback SE method does not have this limitation.

Test the command line to ensure scripts are enabled and the password is working:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "C:\ABC Software\ABC Postgresql 12\psql_backup.ps1"

In Task Scheduler:

1. Create Basic Task
2. Enter Backup ABC SQL in Name. Click Next.
3. Select Daily and click Next.
4. Set start date and time and click Next.
5. Select Start a program and click Next.
6. Enter C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe for the program.
7. Enter -File "C:\ABC Software\ABC Postgresql 12\psql_backup.ps1" for Add arguments.
8. Click Next and Finish.
9. Test by running the task.
10. Monitor daily backups.