here iam taking snapshot postgresql data directory but i don’t know wether it is correct or not can anyone help
Take a look at the documentation if you want to backup the database files directly. You either need to shutdown the server before the backup or create a filesystem snapshot that you can backup. Otherwise you are risking corruption. This has nothing to do with Kopia itself but with databases in general.
A SQL dump is generally the better approach.
thank you
But here without stopping postgresql server iam taking snapshot of this psql data directory /var/lib/postgresql/14/main/ it workings it may cause any problems or issue while going on
You can safely assume that it is useless and when you restore it you will get corrupted database.
@dimejo already posted how it should be approached to do this right.
Here something to read to understand why what you are doing is wrong:
If you’re talking about a Kopia snapshot, then you’ll be operating on files, which are currently in use and subject to change. It may work, but it is really rather unlikely - I wouldn’t bet my DB on that.
Even a filesystem snapshot, e.g. like ZFS does, is still rather insecure, because there will be data in memory, which may has not been written to disk. At least this would amount to the same issue as if your database process suddenly died, so a recover can/will be performed, which might loose you the last transaction. If the db was not busy at that time, you may get away with it, though. Might be good enough for non-serious use, but not if you’re depending on the data in that database,
This leaves the database itself performing a dump to the filesystem. At least this dump will be consistent and the dump files can then safely be grabbed by Kopia. In MySQL you can lock all tables for the duration of the dump and I assume, that postgresql has some similar feature - make sure to use it.
Thankyou guys
is any other tool to take incremental backup of databse either paid or free
any suggestions