PostgreSQL Query To Create A Directory

Question

Files are being written to a directory using the COPY query:

Copy (SELECT * FROM animals) To '/var/lib/postgresql/data/backups/2020-01-01/animals.sql' With CSV DELIMITER ',';

However if the directory 2020-01-01 does not exist, we get the error

could not open file "/var/lib/postgresql/data/backups/2020-01-01/animals.sql" for writing: No such file or directory

PostgeSQL server is running inside a Docker container with the volume mapping /mnt/backups:/var/lib/postgresql/data/backups

The Copy query is being sent from a Node.js app outside of the Docker container.

The mapped host directory /mnt/backups was created by Docker Compose and is owned by root, so the Node.js app sending the COPY query is unable to create the missing directories due to insufficient permissions.

The backup file is meant to be transferred out of the Docker container to the Docker host.

Question: Is it possible to use an SQL query to ask PostgreSQL 11.2 to create a directory if it does not exist? If not, how will you recommend the directory creation be done?

Using Node.js 12.14.1 on Ubuntu 18.04 host. Using PostgreSQL 11.2 inside container, Docker 19.03.5

does this file need to be created in the server? would it be an option to create it directly in the client machine? — Jim Jones
– Jim Jones, Commented Jan 28, 2020 at 19:54
@JimJones It can be created directly in the client machine (Ubuntu, also the Docker host). Do you expect it to be significantly slower if created on the client machine? — Nyxynyx
– Nyxynyx, Commented Jan 28, 2020 at 19:56
there will be no difference in speed. i will add an answer for you to try it out — Jim Jones
– Jim Jones, Commented Jan 28, 2020 at 19:59
@JimJones There may be a difference in speed depending on the efficiency of the network and client. With to stdin the data must be transferred from the server to the client then to disk. With to 'file' it goes directly to disk. — Schwern
– Schwern, Commented Jan 28, 2020 at 20:05
Postgres has its own backup and restore utilities. They are likely to be a better choice than building your own. — Schwern
– Schwern, Commented Jan 28, 2020 at 20:07

Jim Jones · Accepted Answer · 2020-01-28 20:38:29Z

2

An easy way to solve it is to create the file directly into the client machine. Using STDOUT from COPY you can let the query output be redirected to the client standard output, which you can catch and save in a file. For instance, using psql in the client machine:

$ psql -U your_user -d your_db -c "COPY (SELECT * FROM animals) TO STDOUT WITH CSV DELIMITER ','" > file.csv

Creating an output directoy in case it does not exist:

$ mkdir -p /mnt/backups/2020-01/ && psql -U your_user -d your_db -c "COPY (SELECT * FROM animals) TO STDOUT WITH CSV DELIMITER ','" > /mnt/backups/2020-01/file.csv

On a side note: try to avoid exporting files into the database server. Although it is possible, I consider it a bad practice. Doing so you will either write a file into the postgres system directories or give the postgres user permission to write somewhere else, and it is something you shouldn't be comfortable with. Export data directly to the client either using COPY as I mentioned or follow the advice from @Schwern. Good luck!

edited Jan 28, 2020 at 20:38

answered Jan 28, 2020 at 20:01

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nyxynyx Over a year ago

Just tried your command, seems like psql does not create the directories for you. /bin/sh: 1: cannot create /mnt/backups/2020-01/animals.sql: Directory nonexistent. Is there an argument we can pass to psql or must we use mkdir -p <dirname> before running the psql command?

Nyxynyx Over a year ago

Nope, just wondering if everything can be neatly done in 1 single command.

Jim Jones Over a year ago

Well, I would go for writing mkdir -p your_directory_path before the psql command. This would create the directory for you in case it does not exist

Jim Jones Over a year ago

@Nyxynyx I just added a line to my answer that might to what you want:) good luck

Nyxynyx Over a year ago

Thanks. I've also modified the psql command to use multi-threaded compression with psql .... | pigz -rsyncable > /mnt/backups/2020-01/animals.csv

Schwern · Accepted Answer · 2020-01-28 20:23:05Z

Postgres has its own backup and restore utilities which are likely to be a better choice than rolling your own.

When used with one of the archive file formats and combined with pg_restore, pg_dump provides a flexible archival and transfer mechanism. pg_dump can be used to backup an entire database, then pg_restore can be used to examine the archive and/or select which parts of the database are to be restored. The most flexible output file formats are the “custom” format (-Fc) and the “directory” format (-Fd). They allow for selection and reordering of all archived items, support parallel restoration, and are compressed by default. The “directory” format is the only format that supports parallel dumps.

A simple backup rotation script might look like this:

#!/bin/sh

table='animals'
url='postgres://username@host:port/database_name'
date=`date -Idate`
file="/path/to/your/backups/$date/$table.sql"

mkdir -p `dirname $file`

pg_dump $url -w -Fc --table=$table -f $file

To avoid hard coding the database password, -w means it will not prompt for a password and instead look for a password file. Or you can use any of many Postgres authentication options.

Collectives™ on Stack Overflow

PostgreSQL Query To Create A Directory

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related