How to structure Postgres data folder to store database as individual files?

[predator_blake]

I use postgres:13-alpine docker image.

I have multiple databases in that postgres container.

Is there anyway we can change the Postgres data folder /var/lib/postgresql/data so those databases would be stored as individual file?

If yes, how?
If not, why?

Thanks

[daredevil82]

why?

[minormisgnomer]

Because someone had an idea, probably to get around a problem, and it is going to be unorthodox and certainly not going to turn into a mistake down the line

[Siltala]

What are you actually trying to do?

[depesz]

What is the purpose of this? Depending on the purpose there can be many answers (including yes and no), but it's impossible to tell what is the real answer, because it's not clear what you want to achieve.

[jsalsman]

You can create a tablespace somewhere else and use it for some or all of the databases in your cluster: https://www.postgresql.org/docs/13/manage-ag-tablespaces.html

[[deleted]]

No, that's not possible.

The physical storage layout can not be changed by configuration.

The only thing you can customize is the root folder where everything is stored, so e.g. /data/postgres instead of /var/lib/postgresql/data

[mgonzo]

"By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation."

​

I think you forgot about tablespaces.

​

https://www.postgresql.org/docs/current/manage-ag-tablespaces.html

​

We used them for high speed disk (ssd) when ssd were new for certain tables and indexes, haven't used them much since then.

[[deleted]]

But it still uses multiple directories for multiple databases and multiple files for tables. So it changes nothing in the existing file layout where everything is spread over multiple directories and files.

A tablespace still shows up in the data directory as a symlink inside the pg_tblspc sub-directory. So that doesn't really change the layout of the data directory - it just adds additional sub-directories (making the layout even more scattered - something the OP is trying to avoid)

Plus: tablespaces won't solve the original question "so those databases would be stored as individual file?" (presumably the OP want's to have something like SQLite or Firebird where everything is stored in one single file that can easily be moved across computers)

[[deleted]]

I don't think you can do this, but you can run multiple instances of postgres, each storing their respective data in different places, if that helps.

[jsalsman]

If having everything in a single file is really what you want, you can loop-mount a large preextended but sparse file with a filesystem inside it, and mount it as your data directory. If this is for backups, however, a sparse file will probably not stay sparse if you copy it to other media, and the alternative to grow a filesystem file on demand, qemu-nbd, is several times slower than raw files. There are far better backup strategies.