Thread
-
initdb when data/ folder has mount points
Ron Johnson <ron.l.johnson@cox.net> — 2018-02-21T22:38:53Z
Hi, v9.6.6 Apparently, initdb assumes that data/ is one big mount point. However, we have four mount points: /var/lib/pgsql/9.6/data/backup /var/lib/pgsql/9.6/data/base /var/lib/pgsql/9.6/data/pg_log /var/lib/pgsql/9.6/data/pg_xlog They are all empty. How do I convince it to ignore the fact that the directories exist? Thanks -- Money Angular momentum makes the world go 'round.
-
Re: initdb when data/ folder has mount points
Rich Shepard <rshepard@appl-ecosys.com> — 2018-02-21T23:17:12Z
On Wed, 21 Feb 2018, Ron Johnson wrote: > Apparently, initdb assumes that data/ is one big mount point. However, we > have four mount points: > /var/lib/pgsql/9.6/data/backup > /var/lib/pgsql/9.6/data/base > /var/lib/pgsql/9.6/data/pg_log > /var/lib/pgsql/9.6/data/pg_xlog Ron, What command do you use? Here, on Slackware-14.2, /var/lib/psql/10.2/data/ contains: PG_VERSION pg_ident.conf pg_snapshots pg_wal base pg_logical pg_stat pg_xact global pg_multixact pg_stat_tmp postgresql.auto.conf pg_commit_ts pg_notify pg_subtrans postgresql.conf pg_dynshmem pg_replslot pg_tblspc postmaster.opts pg_hba.conf pg_serial pg_twophase postmaster.pid The 9.6 version was the same. The command I use (as user postgres) is: initdb -D /var/lib/pgsql/10.2/data & HTH, Rich
-
Re: initdb when data/ folder has mount points
Tom Lane <tgl@sss.pgh.pa.us> — 2018-02-22T00:01:24Z
Ron Johnson <ron.l.johnson@cox.net> writes: > Apparently, initdb assumes that data/ is one big mount point. However, we > have four mount points: > /var/lib/pgsql/9.6/data/backup > /var/lib/pgsql/9.6/data/base > /var/lib/pgsql/9.6/data/pg_log > /var/lib/pgsql/9.6/data/pg_xlog Don't do that. There's no reason for backup storage to be under the data directory (and lots of good reasons for it not to be). Just put it somewhere else. The supported way to put pg_xlog on a separate volume is to put that mount point somewhere else, and make $PGDATA/pg_xlog be a symlink to it. IIRC, there's an initdb option to help with that, though you can also make it so manually after initdb. For pg_log, just put it somewhere else and set the appropriate configuration option to say where to write the postmaster log files. Or you could use a symlink, like the solution for pg_xlog, but I don't see any advantage there. I don't see any point in making base/ be its own mount point. Once you get rid of those other subdirectories there's not going to be enough "global" storage left to justify its own volume. regards, tom lane
-
Re: initdb when data/ folder has mount points
Ron Johnson <ron.l.johnson@cox.net> — 2018-02-22T00:18:15Z
On 02/21/2018 06:01 PM, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> Apparently, initdb assumes that data/ is one big mount point. However, we >> have four mount points: >> /var/lib/pgsql/9.6/data/backup >> /var/lib/pgsql/9.6/data/base >> /var/lib/pgsql/9.6/data/pg_log >> /var/lib/pgsql/9.6/data/pg_xlog > Don't do that. > > There's no reason for backup storage to be under the data directory (and > lots of good reasons for it not to be). Just put it somewhere else. > > The supported way to put pg_xlog on a separate volume is to put that > mount point somewhere else, and make $PGDATA/pg_xlog be a symlink to > it. IIRC, there's an initdb option to help with that, though you can > also make it so manually after initdb. > > For pg_log, just put it somewhere else and set the appropriate > configuration option to say where to write the postmaster log files. > Or you could use a symlink, like the solution for pg_xlog, but > I don't see any advantage there. > > I don't see any point in making base/ be its own mount point. Once > you get rid of those other subdirectories there's not going to be > enough "global" storage left to justify its own volume. I'm replicating the structure in our existing systems. Is there an (up to date) Best Practices document for these kinds of issues? -- Money Angular momentum makes the world go 'round.
-
Re: initdb when data/ folder has mount points
Tom Lane <tgl@sss.pgh.pa.us> — 2018-02-22T00:47:16Z
Ron Johnson <ron.l.johnson@cox.net> writes: > On 02/21/2018 06:01 PM, Tom Lane wrote: >> Ron Johnson <ron.l.johnson@cox.net> writes: >>> Apparently, initdb assumes that data/ is one big mount point. However, we >>> have four mount points: >>> /var/lib/pgsql/9.6/data/backup >>> /var/lib/pgsql/9.6/data/base >>> /var/lib/pgsql/9.6/data/pg_log >>> /var/lib/pgsql/9.6/data/pg_xlog >> Don't do that. > I'm replicating the structure in our existing systems. Is there an (up to > date) Best Practices document for these kinds of issues? Well, initdb has never allowed a non-empty target (at least not in this century). So your existing setup must have been achieved by manual hacking post-initdb, which you could certainly do again if you're dead set on that layout. I remain of the opinion that it's not a good design, though. regards, tom lane
-
Re: initdb when data/ folder has mount points
David Steele <david@pgmasters.net> — 2018-02-22T00:56:38Z
On 2/21/18 7:01 PM, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> Apparently, initdb assumes that data/ is one big mount point. However, we >> have four mount points: >> /var/lib/pgsql/9.6/data/backup >> /var/lib/pgsql/9.6/data/base >> /var/lib/pgsql/9.6/data/pg_log >> /var/lib/pgsql/9.6/data/pg_xlog > > Don't do that. Agreed. > There's no reason for backup storage to be under the data directory (and > lots of good reasons for it not to be). Just put it somewhere else. Yes -- in this configuration your backups would be backed up with every backup. It's pretty obvious where that would go. > The supported way to put pg_xlog on a separate volume is to put that > mount point somewhere else, and make $PGDATA/pg_xlog be a symlink to > it. IIRC, there's an initdb option to help with that, though you can > also make it so manually after initdb. initdb supports linking pg_xlog/pg_wal with the --xlogdir/--waldir option. > For pg_log, just put it somewhere else and set the appropriate > configuration option to say where to write the postmaster log files. > Or you could use a symlink, like the solution for pg_xlog, but > I don't see any advantage there. Symlinking pg_log is not ideal because the logs end up in the backup. It gets pretty weird when those logs get restored to a standby and somebody starts reading them. > I don't see any point in making base/ be its own mount point. Once > you get rid of those other subdirectories there's not going to be > enough "global" storage left to justify its own volume. Agreed. -- -David david@pgmasters.net
-
Re: initdb when data/ folder has mount points
Michael Paquier <michael@paquier.xyz> — 2018-02-22T06:16:39Z
On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote: > On 2/21/18 7:01 PM, Tom Lane wrote: >> For pg_log, just put it somewhere else and set the appropriate >> configuration option to say where to write the postmaster log files. >> Or you could use a symlink, like the solution for pg_xlog, but >> I don't see any advantage there. > > Symlinking pg_log is not ideal because the logs end up in the backup. It > gets pretty weird when those logs get restored to a standby and somebody > starts reading them. log_directory in postgresql.conf san be set up with an absolute directory value. So there is no actual need for a symlink with pg_log. This also reduces the amount of data transfered as part of base backups without actually needing them. -- Michael
-
Re: initdb when data/ folder has mount points
David Steele <david@pgmasters.net> — 2018-02-22T13:22:04Z
On 2/22/18 1:16 AM, Michael Paquier wrote: > On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote: >> On 2/21/18 7:01 PM, Tom Lane wrote: >>> For pg_log, just put it somewhere else and set the appropriate >>> configuration option to say where to write the postmaster log files. >>> Or you could use a symlink, like the solution for pg_xlog, but >>> I don't see any advantage there. >> >> Symlinking pg_log is not ideal because the logs end up in the backup. It >> gets pretty weird when those logs get restored to a standby and somebody >> starts reading them. > > log_directory in postgresql.conf san be set up with an absolute > directory value. So there is no actual need for a symlink with pg_log. > This also reduces the amount of data transfered as part of base > backups without actually needing them. Yes, I really should have said having pg_log in the data directory at all is not ideal, symlinked or no. -- -David david@pgmasters.net
-
Re: initdb when data/ folder has mount points
Ron Johnson <ron.l.johnson@cox.net> — 2018-02-22T14:21:04Z
On 02/22/2018 07:22 AM, David Steele wrote: > On 2/22/18 1:16 AM, Michael Paquier wrote: >> On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote: >>> On 2/21/18 7:01 PM, Tom Lane wrote: >>>> For pg_log, just put it somewhere else and set the appropriate >>>> configuration option to say where to write the postmaster log files. >>>> Or you could use a symlink, like the solution for pg_xlog, but >>>> I don't see any advantage there. >>> Symlinking pg_log is not ideal because the logs end up in the backup. It >>> gets pretty weird when those logs get restored to a standby and somebody >>> starts reading them. >> log_directory in postgresql.conf san be set up with an absolute >> directory value. So there is no actual need for a symlink with pg_log. >> This also reduces the amount of data transfered as part of base >> backups without actually needing them. > Yes, I really should have said having pg_log in the data directory at > all is not ideal, symlinked or no. Is there a Best Practices wiki? (https://wiki.postgresql.org/wiki/Detailed_installation_guides is pretty old and thin.) -- Money Angular momentum makes the world go 'round.