Recommended way to copy database files on Windows OS (to perform file system level backup)

A bit of pedanticism:

So we would like to know how you recommend copying PostgreSQL database

files in Windows OS to perform file system level backups.

(For Example – The recommended way in Linux is to use tar format.)

That is not what a file-system-level back up is, and not what tar does at
all. Tar basically creates a file that contains other files, and would be
subject to the same issues a copy would be.

A file-system level backup is just that, a backup done by the file system
itself, usually via an atomic snapshot. LVM, ZFS, and BTFS are all capable
of doing this in Linux and *BSD. The filesystem will store the current
state of the filesystem somewhere in an atomic manner, i.e. nothing else
happens when the snapshot is made.

I think NTFS can do volume shadow copies, but I havn't used windows in
perhaps a decade and have never done so as an admin or professionally.

Jim

On Mon, May 14, 2018 at 7:22 AM, Yashwanth Govinda Setty <
ygovindasetty@commvault.com> wrote:

On Mon, May 14, 2018 at 2:22 PM, Yashwanth Govinda Setty <
ygovindasetty@commvault.com> wrote:

Hi all,

We are facing this problem while performing file system level backup of
database files:

As each database will form a directory inside Base directory which
consists of files representing the tables, when some tables are dropped
during backup, *We get error while copying since the files do not exist
anymore*.

So we would like to know how you recommend copying PostgreSQL database
files in Windows OS to perform file system level backups.

(For Example – The recommended way in Linux is to use tar format.)

Thanks,

*Yashwanth*

***************************Legal Disclaimer***************************
"This communication may contain confidential and privileged material for
the
sole use of the intended recipient. Any unauthorized review, use or
distribution
by others is strictly prohibited. If you have received the message by
mistake,
please advise the sender by reply email and delete the message. Thank you."
**********************************************************************

An easy way to perform backup of your data is to use PostgreSQL's very own
pg_dump utility.

## Yashwanth Govinda Setty (ygovindasetty@commvault.com):

We are facing this problem while performing file system level backup of database files:
As each database will form a directory inside Base directory which consists of files representing the tables, when some tables are dropped during backup, We get error while copying since the files do not exist anymore.

This looks like you read only the first sentence of the relevant
documentation:
https://www.postgresql.org/docs/current/static/backup-file.html

The "recommended way" is not "use tar", but to heed both restrictions
mentioned there. Especially, if you want to do a naive copy of the files,
the database has to be shut down.
If shutting down the database is not an option (most cases), your
option is a "base backup" - there's pg_basebackup to help you with
that, and you can do that "the hard way" with the low level API.
Both approaches are described here:
https://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-BASE-BACKUP

This is mostly independent from the OS - but you have to make sure to
follow the process (shutting down the database beforehand, or do the
full base backup) - else the database will be corrupted after restore
and may even fail to start.

Regards,
Christoph

--
Spare Space.

An easy way to perform backup of your data is to use PostgreSQL's very
own
pg_dump utility.

That's not a file level backup, but a logical backup. Maybe he wants to build standby for streaming replication, for this you needs an other backup. See the answer from Christoph.

Andreas

--
2ndQuadrant - The PostgreSQL Support Company