Bug 1247477 - postgresql-setup fails when /var/lib/pgsql/data is mount point
Summary: postgresql-setup fails when /var/lib/pgsql/data is mount point
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Fedora
Classification: Fedora
Component: postgresql
Version: 22
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
Assignee: Pavel Raiskup
QA Contact: Fedora Extras Quality Assurance
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2015-07-28 06:11 UTC by Miroslav Suchý
Modified: 2015-10-04 19:15 UTC (History)
6 users (show)

Fixed In Version: postgresql-9.4.4-4.fc23
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2015-10-04 19:15:39 UTC
Type: Bug
Embargoed:


Attachments (Terms of Use)

Description Miroslav Suchý 2015-07-28 06:11:51 UTC
Description of problem:
In my setup /var/lib/pgsql/data is mounted volume. When I run postgresql-setup I get an error.

Version-Release number of selected component (if applicable):
postgresql-upgrade-9.4.4-1.fc22.x86_64

How reproducible:
deterministic

Steps to Reproduce:
1. mount /dev/sdb1 /var/lib/pgsql/data
2. have old db format in /var/lib/pgsql/data
3. postgresql-setup --upgrade

Actual results:
mv: cannot move '/var/lib/pgsql/data' to '/var/lib/pgsql/data-old': Device or resource busy

Expected results:
No errors.

Comment 1 Tom Lane 2015-07-28 14:10:18 UTC
> In my setup /var/lib/pgsql/data is mounted volume.

I'd write this off as "not a bug", because there are many reasons why such a setup is a very bad idea:

1. If the filesystem uses a lost+found directory, initdb won't even let you create a data directory there, because it will see the directory as nonempty to begin with.  (And I think initdb knows about some other ways to detect mount points, as well, and rejects them.  How did you even create this state of affairs to start with?)

2. Mount points are generally root-owned for security reasons, but if the filesystem root directory is also a data directory, you can't make it work like that.

3. If, some day, the filesystem is accidentally unmounted while the database is up, it will continue to write into files that are now getting placed in the mount-point directory on the parent volume.  This usually results in an unrecoverably messed-up database by the time you realize what's going wrong.  (There are horror stories about such cases in the PG community mailing list archives, dating from before we installed the don't-use-a-mount-point defenses in initdb.)

It's far safer to put the data directory one level down, as a postgres-owned directory within the root-owned filesystem root directory, rather than trying to equate it to the root directory.

In fact, if you want pg_upgrade to work, the data directory actually has to itself be within a postgres-owned directory, so that pg_upgrade can "mv" it without root privileges.  (This is one reason why the standard arrangement is for the data directory to be within postgres' $HOME directory, not be the $HOME directory itself.)  So you want two levels of directory below the mount point.

Comment 2 Miroslav Suchý 2015-07-28 14:52:17 UTC
(In reply to Tom Lane from comment #1)
> to begin with.  (And I think initdb knows about some other ways to detect
> mount points, as well, and rejects them.  How did you even create this state
> of affairs to start with?)
>
> 2. Mount points are generally root-owned for security reasons, but if the
> filesystem root directory is also a data directory, you can't make it work
> like that.

With few commandline executions you can do that :)
 
> It's far safer to put the data directory one level down, as a postgres-owned
> directory within the root-owned filesystem root directory, rather than
> trying to equate it to the root directory.
> 
> In fact, if you want pg_upgrade to work, the data directory actually has to
> itself be within a postgres-owned directory, so that pg_upgrade can "mv" it
> without root privileges.  (This is one reason why the standard arrangement
> is for the data directory to be within postgres' $HOME directory, not be the
> $HOME directory itself.)  So you want two levels of directory below the
> mount point.

This sounds reasonable. I would be perfectly happy if postgresql-setup would print
  /var/lib/pgsql/data is mountpoint. This is not supported and discouraged.
  See https://fooo
where https://fooo would contains those two last paragraph with your explanation.

instead of:
  cannot move, device or resource busy.

You just educated one guy (me), but there is bunch of others waiting in queue :)

Comment 3 Tom Lane 2015-07-29 01:45:33 UTC
(In reply to Miroslav Suchý from comment #2)
> You just educated one guy (me), but there is bunch of others waiting in
> queue :)

Fair point --- I was a bit startled to realize that this issue isn't addressed anywhere in the PG user documentation.  I've done something about that:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8c72a7fab47a7f501d211468d6e477e1f3a20599

http://www.postgresql.org/docs/devel/static/creating-cluster.html

although this wording won't propagate into release docs until whenever we do the next minor releases.

Not sure whether it's worth teaching the postgresql-setup script to emit such a specific error message as you suggest.  I'd think not, but it's not my code to fix.

Comment 4 Pavel Raiskup 2015-09-25 08:04:09 UTC
Thanks for this discussion Mirek & Tom, would you consider this warning
as sufficient?

 * Initializing database in '/var/lib/pgsql/data'
WARNING: Note that either your data directory '/var/lib/pgsql/data' or
         the parent directory '/var/lib/pgsql'
         is direct mountpoint.  This is usually bad idea and your
         filesystem layout should ideally look like:
         /ROOT_OWNED_MOUNTPOINT/POSTGRES_OWNED_DIRECTORY/DATADIR.
         See upstream documentation for more info:
         http://www.postgresql.org/docs/9.4/static/creating-cluster.html
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Comment 5 Miroslav Suchý 2015-09-25 08:11:02 UTC
Sounds fine to me.

Comment 7 Fedora Update System 2015-09-25 13:49:20 UTC
postgresql-9.4.4-4.fc23 has been submitted as an update to Fedora 23. https://bodhi.fedoraproject.org/updates/FEDORA-2015-ba6b14d36d

Comment 8 Tom Lane 2015-09-25 14:30:39 UTC
"is a direct mountpoint" and "usually a bad idea" would be slightly better English, otherwise it's fine.

Comment 10 Fedora Update System 2015-09-27 00:54:50 UTC
postgresql-9.4.4-4.fc23 has been pushed to the Fedora 23 testing repository. If problems still persist, please make note of it in this bug report.
If you want to test the update, you can install it with
$ su -c 'dnf --enablerepo=updates-testing update postgresql'
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-2015-ba6b14d36d

Comment 11 Fedora Update System 2015-10-04 19:15:32 UTC
postgresql-9.4.4-4.fc23 has been pushed to the Fedora 23 stable repository. If problems still persist, please make note of it in this bug report.


Note You need to log in before you can comment on or make changes to this bug.