Thread: Recommended way to copy database files on Windows OS (to perform filesystem level backup)

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

From

Yashwanth Govinda Setty

Date:

14 May 2018, 17:22:22

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."

**********************************************************************

Re: Recommended way to copy database files on Windows OS (to performfile system level backup)

From

James Keener

Date:

14 May 2018, 19:43:36

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:

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."
**********************************************************************

Re: Recommended way to copy database files on Windows OS (to performfile system level backup)

From

Allan Kamau

Date:

14 May 2018, 19:48:45

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.

Re: Recommended way to copy database files on Windows OS (to performfile system level backup)

From

Christoph Moench-Tegeder

Date:

14 May 2018, 20:14:27

## 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
sometables 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.

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

From

Andreas Kretschmer

Date:

14 May 2018, 23:01:18

>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
thisyou needs an other backup. See the answer from Christoph. 

Andreas

--
2ndQuadrant - The PostgreSQL Support Company