Database backup and restore in pg xbade++

[unixkd]

Hi all

Any member with xbase++ procedure/function/class for backup and restore of databases in postgresql.

Thanks

Joe

[k-insis]

- Backup is done with pg_dump utility
- Restore is done with psql / pg_restore utilities.
- Partial backups / restores on schema, table, procedures, views, etc are of course doable.

Those utils handle every complexity that is on server.

Interactive way can be done in dbeaver .

Just out of curiosity - Why would you need to have such utility in xpp ?

Hi all

Any member with xbase++ procedure/function/class for backup and restore of databases in postgresql.

Thanks

Joe

[Tom]

You may directly create a backup of the physical files where your database is stored. Open pgAdmin, select your database and open the query tool. Type this command:

Code: Select all

show data_directory;

It shows where the database physically resides on your machine. Backup this folder. In case, you may also restore those files.

[k-insis]

This is not advisable method by Postgresql official documentation:

https://www.postgresql.org/docs/16/backup-file.html

You may directly create a backup of the physical files where your database is stored. Open pgAdmin, select your database and open the query tool. Type this command:

Code: Select all

show data_directory;

It shows where the database physically resides on your machine. Backup this folder. In case, you may also restore those files.

[Tom]

Those limitations are easy to handle. And I didn't say that this is best practice; it's just a way to create a backup. You don't need to have an active connection to your server to do that. Besides, the statement can be used from inside an Xbase++-application (same with dump, I know).

[unixkd]

Hi Tom

Besides, the statement can be used from inside an Xbase++-application

I need to use runshell ??

My executables .EXEs are on the workstations not on the server. I prefer it that way in case my application need to access data in the cloud.

Thanks

Joe

[Tom]

Hi, Joe.

I need to use runshell ??

No. That is a valid (PG-)SQL statement. Depending on how you connect to the PG server from inside your application, you may just send this statement to the server and analyze the result.

[unixkd]

Hi Tom

No. That is a valid (PG-)SQL statement. Depending on how you connect to the PG server from inside your application, you may just send this statement to the server and analyze the result.

I try this but get error.

oConn:execute("pg_dump -u powerplus -d powerplusdb -f e:/backup_file.sql")

where:
powerplus is db user name
powerplusdb is database name

e:\backup_file.sql is my backup file to drive e:

What Am I doing wrong ?

Thanks

Joe

[k-insis]

Mr. Joe you are wrong in basic concept of how pgdbe server access works.

This is client server and you do not execute shell commands via SQL command interface as that would be a huge security no-no if implemented.

To start backups/restores from your application you have to do that on client side or fully on side of server (as needed). Even if application is running on same machine as pgsql server does.

That is why I pointed you to commands PostgreSQL\15\bin\pg_dump.exe to backup/resotore.

So you will need content of PostgreSQL\15\bin (client at least) onto your installation and do this

@echo off
set PGPASSWORD=some_secret_pass
bin\pg_dump.exe -h IP_orHostnameOfServer -Upostgresusername --file=E:\backupfiledump.sql name_of_database_to_dump

which will produce dump file with structures, sequences (very important), schemas, data and relations/constraints.

Hi Tom

No. That is a valid (PG-)SQL statement. Depending on how you connect to the PG server from inside your application, you may just send this statement to the server and analyze the result.

I try this but get error.

oConn:execute("pg_dump -u powerplus -d powerplusdb -f e:/backup_file.sql")

where:
powerplus is db user name
powerplusdb is database name

e:\backup_file.sql is my backup file to drive e:

What Am I doing wrong ?

Thanks

Joe

[Tom]

I try this but get error.

My suggestion was not: Go and create a dump and save this using a SQL-command. This won't work, as k-insis mentioned. My suggestion was to retrieve the name and path of the physical files using an SQL-statement (like the one I showed) and create a backup by copying those file(s). You may also create a dump file (.SQL) and copy this, but only creating this file will take some time, and it will be much bigger than the physical files. Besides, it will contain data readable by humans and importable by other servers. You will need another mechanism to protect this file(s). The physical files can only be accessed if username and password of the server are known.