Issue: Creating Symlink for data directory of postgresql in CentOS7

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Issue: Creating Symlink for data directory of postgresql in CentOS7

Chatterjee, Shibayan

Hi,

I’m trying to create a symlink for ‘data’ directory present in ‘/var/lib/pgsql/’, pointing to different location.

After providing exact access permissions and ownership to postgres, I’m still unable to restart postgres after modifications.

 

I’ve also modified ‘/var/lib/pgsql/postgresql.conf’ and ‘/usr/lib/systemd/system/postgresql.service’ to point the $PGDATA location to the actual destination as respectively:

 

data_directory = '/data/postgresql/data'

Environment=PGDATA=/data/postgresql/data

 

 

Can you guys help ? I tried searching a solution for this problem but still couldn’t get a perfect solution.

 

Here’s what I’m seeing in:

>>> systemctl status postgresql.service

 

postgresql.service - PostgreSQL database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)

   Active: failed (Result: exit-code) since Fri 2019-07-12 10:00:51 UTC; 38s ago

  Process: 8852 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Main PID: 29483 (code=exited, status=0/SUCCESS)

 

Jul 12 10:00:51 systemd[1]: Starting PostgreSQL database server...

Jul 12 10:00:51 systemd[1]: postgresql.service: control process exited, code=exited status=1

Jul 12 10:00:51 systemd[1]: Failed to start PostgreSQL database server.

Jul 12 10:00:51 systemd[1]: Unit postgresql.service entered failed state.

Jul 12 10:00:51 systemd[1]: postgresql.service failed.

 

>>> /var/log/messages

 

Jul 12 10:00:51 systemd: Starting PostgreSQL database server...

Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing or empty.

Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.

Jul 12 10:00:51 postgresql-check-db-dir: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.

Jul 12 10:00:51 systemd: postgresql.service: control process exited, code=exited status=1

Jul 12 10:00:51 systemd: Failed to start PostgreSQL database server.

Jul 12 10:00:51 systemd: Unit postgresql.service entered failed state.

Jul 12 10:00:51 systemd: postgresql.service failed.

 

 

-Shibayan

 

This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Adrian Klaver-4
On 7/12/19 3:11 AM, Chatterjee, Shibayan wrote:
> Hi,
>
> I’m trying to create a symlink for ‘data’ directory present in
> ‘/var/lib/pgsql/’, pointing to different location.

Can you show the actual symlink and it containing directory?

>
> After providing exact access permissions and ownership to postgres, I’m
> still unable to restart postgres after modifications.
>
> I’ve also modified ‘/var/lib/pgsql/postgresql.conf’ and
> ‘/usr/lib/systemd/system/postgresql.service’ to point the $PGDATA
> location to the actual destination as respectively:
>
> data_directory = '/data/postgresql/data'
>
> Environment=PGDATA=/data/postgresql/data
>
> Can you guys help ? I tried searching a solution for this problem but
> still couldn’t get a perfect solution.
>
> Here’s what I’m seeing in:
>
>>>> systemctl status postgresql.service
>
> postgresql.service - PostgreSQL database server
>
>     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled;
> vendor preset: disabled)
>
>     Active: failed (Result: exit-code) since Fri 2019-07-12 10:00:51
> UTC; 38s ago
>
>    Process: 8852 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA}
> (code=exited, status=1/FAILURE)
>
> Main PID: 29483 (code=exited, status=0/SUCCESS)
>
> Jul 12 10:00:51 systemd[1]: Starting PostgreSQL database server...
>
> Jul 12 10:00:51 systemd[1]: postgresql.service: control process exited,
> code=exited status=1
>
> Jul 12 10:00:51 systemd[1]: Failed to start PostgreSQL database server.
>
> Jul 12 10:00:51 systemd[1]: Unit postgresql.service entered failed state.
>
> Jul 12 10:00:51 systemd[1]: postgresql.service failed.
>
>>>> /var/log/messages
>
> Jul 12 10:00:51 systemd: Starting PostgreSQL database server...
>
> Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is
> missing or empty.
>
> Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb"
> to initialize the database cluster.
>
> Jul 12 10:00:51 postgresql-check-db-dir: See
> /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
>
> Jul 12 10:00:51 systemd: postgresql.service: control process exited,
> code=exited status=1
>
> Jul 12 10:00:51 systemd: Failed to start PostgreSQL database server.
>
> Jul 12 10:00:51 systemd: Unit postgresql.service entered failed state.
>
> Jul 12 10:00:51 systemd: postgresql.service failed.
>
> -Shibayan
>
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the
> sender by reply e-mail and destroy all copies of the communication and
> any attachments.


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Laurenz Albe
In reply to this post by Chatterjee, Shibayan
On Fri, 2019-07-12 at 10:11 +0000, Chatterjee, Shibayan wrote:
> I’m trying to create a symlink for ‘data’ directory present in ‘/var/lib/pgsql/’, pointing to different location.
> After providing exact access permissions and ownership to postgres, I’m still unable to restart postgres after modifications.
>  
> I’ve also modified ‘/var/lib/pgsql/postgresql.conf’ and ‘/usr/lib/systemd/system/postgresql.service’
> to point the $PGDATA location to the actual destination as respectively:
>  
> data_directory = '/data/postgresql/data'
> Environment=PGDATA=/data/postgresql/data

[...]

> >>> /var/log/messages
>  
> Jul 12 10:00:51 systemd: Starting PostgreSQL database server...
> Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing or empty.
> Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.
> Jul 12 10:00:51 postgresql-check-db-dir: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
> Jul 12 10:00:51 systemd: postgresql.service: control process exited, code=exited status=1
> Jul 12 10:00:51 systemd: Failed to start PostgreSQL database server.
> Jul 12 10:00:51 systemd: Unit postgresql.service entered failed state.
> Jul 12 10:00:51 systemd: postgresql.service failed.

So there is some "postgresql-check-db-dir" (not part of PostgreSQL) that complains
that there is nothing in /data/postgresql/data.  Is that accurate?

It looks like you succeeded in getting the startup process to look for the
PostgreSQL data directory in the new location, but - alas - there is no data
directory there yet.

You could follow the instructions and run "initdb" to create the data directory.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Luca Ferrari-2
On Fri, Jul 12, 2019 at 4:47 PM Laurenz Albe <[hidden email]> wrote:
> So there is some "postgresql-check-db-dir" (not part of PostgreSQL) that complains
> that there is nothing in /data/postgresql/data.  Is that accurate?
>

According to this
<https://www.postgresql.org/message-id/attachment/34217/postgresql-check-db-dir>
the script should check PGDATA does exist and contain the PG_VERSION
file or complains as shown in the logs.

> It looks like you succeeded in getting the startup process to look for the
> PostgreSQL data directory in the new location, but - alas - there is no data
> directory there yet.What's the content of


What's the content of $PGDATA?

Luca


Reply | Threaded
Open this post in threaded view
|

RE: Issue: Creating Symlink for data directory of postgresql in CentOS7

Chatterjee, Shibayan
Luca,
Here's what I modified the $PGDATA to:

>>> Environment=PGDATA=/data/postgresql/data


-Shibayan

-----Original Message-----
From: Luca Ferrari <[hidden email]>
Sent: Friday, July 12, 2019 11:03 AM
To: Laurenz Albe <[hidden email]>
Cc: Chatterjee, Shibayan <[hidden email]>; [hidden email]
Subject: Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

On Fri, Jul 12, 2019 at 4:47 PM Laurenz Albe <[hidden email]> wrote:
> So there is some "postgresql-check-db-dir" (not part of PostgreSQL)
> that complains that there is nothing in /data/postgresql/data.  Is that accurate?
>

According to this
<https://www.postgresql.org/message-id/attachment/34217/postgresql-check-db-dir>
the script should check PGDATA does exist and contain the PG_VERSION file or complains as shown in the logs.

> It looks like you succeeded in getting the startup process to look for
> the PostgreSQL data directory in the new location, but - alas - there
> is no data directory there yet.What's the content of


What's the content of $PGDATA?

Luca


This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
Reply | Threaded
Open this post in threaded view
|

RE: Issue: Creating Symlink for data directory of postgresql in CentOS7

Chatterjee, Shibayan
In reply to this post by Laurenz Albe
Laurenz,
For sure there's all the necessary files in '/data/postgresql/data'. The startup process cannot read it, because of sym link.
Executing init-db return the below message:

>>>> # sudo postgresql-setup initdb
Data directory is not empty!

-Shibayan

-----Original Message-----
From: Laurenz Albe <[hidden email]>
Sent: Friday, July 12, 2019 8:47 AM
To: Chatterjee, Shibayan <[hidden email]>; [hidden email]
Subject: Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

On Fri, 2019-07-12 at 10:11 +0000, Chatterjee, Shibayan wrote:
> I’m trying to create a symlink for ‘data’ directory present in ‘/var/lib/pgsql/’, pointing to different location.
> After providing exact access permissions and ownership to postgres, I’m still unable to restart postgres after modifications.
>
> I’ve also modified ‘/var/lib/pgsql/postgresql.conf’ and ‘/usr/lib/systemd/system/postgresql.service’
> to point the $PGDATA location to the actual destination as respectively:
>
> data_directory = '/data/postgresql/data'
> Environment=PGDATA=/data/postgresql/data

[...]

> >>> /var/log/messages
>
> Jul 12 10:00:51 systemd: Starting PostgreSQL database server...
> Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing or empty.
> Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.
> Jul 12 10:00:51 postgresql-check-db-dir: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
> Jul 12 10:00:51 systemd: postgresql.service: control process exited,
> code=exited status=1 Jul 12 10:00:51 systemd: Failed to start PostgreSQL database server.
> Jul 12 10:00:51 systemd: Unit postgresql.service entered failed state.
> Jul 12 10:00:51 systemd: postgresql.service failed.

So there is some "postgresql-check-db-dir" (not part of PostgreSQL) that complains that there is nothing in /data/postgresql/data.  Is that accurate?

It looks like you succeeded in getting the startup process to look for the PostgreSQL data directory in the new location, but - alas - there is no data directory there yet.

You could follow the instructions and run "initdb" to create the data directory.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Luca Ferrari-2
On Sat, Jul 13, 2019 at 5:12 AM Chatterjee, Shibayan
<[hidden email]> wrote:
> For sure there's all the necessary files in '/data/postgresql/data'. The startup process cannot read it, because of sym link.
> Executing init-db return the below message:
>

I suspect this could be a systemd problem. What if you try to manually
start the postgres instance?

pg_ctl -D /data/postgresql/data start

What happens then?


> >>>> # sudo postgresql-setup initdb
> Data directory is not empty!

This is of course to prevent you to destroy your system.
I would remove the link, create it to an empty directory and then
initdb such directory as if it is your new database. I imagine that
would start, in such case your data directory is wrong. Can you show
us the content of /data/postgresql/data?

Luca


Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Laurenz Albe
In reply to this post by Chatterjee, Shibayan
On Fri, 2019-07-12 at 18:08 +0000, Chatterjee, Shibayan wrote:
> > Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing or empty.
> > Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.
> > Jul 12 10:00:51 postgresql-check-db-dir: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
>
> For sure there's all the necessary files in '/data/postgresql/data'.
> The startup process cannot read it, because of sym link.

Well, where is the source for this fabled "postgresql-check-db-dir"?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Adrian Klaver-4
On 7/13/19 4:30 AM, Laurenz Albe wrote:
> On Fri, 2019-07-12 at 18:08 +0000, Chatterjee, Shibayan wrote:
>>> Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing or empty.
>>> Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.
>>> Jul 12 10:00:51 postgresql-check-db-dir: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
>>
>> For sure there's all the necessary files in '/data/postgresql/data'.
>> The startup process cannot read it, because of sym link.
>
> Well, where is the source for this fabled "postgresql-check-db-dir"?

https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/9.2/postgresql/EL-7/postgresql92-check-db-dir;h=550b31770cabacf32cbb1b8f272e8ce305fc9908;hb=HEAD

>
> Yours,
> Laurenz Albe
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Laurenz Albe
Adrian Klaver wrote:

> On 7/13/19 4:30 AM, Laurenz Albe wrote:
> > On Fri, 2019-07-12 at 18:08 +0000, Chatterjee, Shibayan wrote:
> > > > Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing or empty.
> > > > Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.
> > > > Jul 12 10:00:51 postgresql-check-db-dir: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
> > >
> > > For sure there's all the necessary files in '/data/postgresql/data'.
> > > The startup process cannot read it, because of sym link.
> >
> > Well, where is the source for this fabled "postgresql-check-db-dir"?
>
> https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/9.2/postgresql/EL-7/postgresql92-check-db-dir;h=550b31770cabacf32cbb1b8f272e8ce305fc9908;hb=HEAD

Thanks.  I read this:

  30 # Check for the PGDATA structure
  31 if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
  32 then
     [...]
  49 else
  50     # No existing PGDATA! Warn the user to initdb it.
  51     echo $"\"$PGDATA\" is missing or empty."
  52     echo $"Use \"/usr/pgsql-9.2/bin/postgresql92-setup initdb\" to initialize the database cluster."
  53     echo $"See $PGDOCDIR/README.rpm-dist for more information."
  54     exit 1
  55 fi

That means that either there was no regular file /data/postgresql/data/PG_VERSION
or no directory /data/postgresql/data/base, or that the user running the script
lacked the permissions to access them.

Since you say that there was a regular data directory there, that would point
to permission problems.

Witn that information, it should be simple to debug the problem.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Luca Ferrari-2
On Sun, Jul 14, 2019 at 5:05 PM Laurenz Albe <[hidden email]> wrote:
> Since you say that there was a regular data directory there, that would point
> to permission problems.

I'm not a systemd expert, but since we are _before_ starting the
cluster, the checks should be run as superuser, so I don't see how a
permission problem could cause this. However, I would like to see the
output of an `ls -l` on such link and directory because I believe
there's rahter a nesting problem (i.e., the PGDATA is somehwere under
another directory).

Luca


Reply | Threaded
Open this post in threaded view
|

RE: Issue: Creating Symlink for data directory of postgresql in CentOS7

Chatterjee, Shibayan
In reply to this post by Laurenz Albe
I found a work around for the problem:
After changing access permissions and ownership of the symlink data directory, I logged into postgres using 'postgres' login as:

>>> sudo -i -u postgres

Then force started postgres from there as mentioned below. The execution doesn't return back to prompt, so had it run back in the background. Postgres started running back to normal.

>>> /usr/bin/postgres -D /var/lib/pgsql/data &

Thanks !
-Shibayan

-----Original Message-----
From: Laurenz Albe <[hidden email]>
Sent: Sunday, July 14, 2019 9:05 AM
To: Adrian Klaver <[hidden email]>; Chatterjee, Shibayan <[hidden email]>; [hidden email]
Subject: Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

Adrian Klaver wrote:

> On 7/13/19 4:30 AM, Laurenz Albe wrote:
> > On Fri, 2019-07-12 at 18:08 +0000, Chatterjee, Shibayan wrote:
> > > > Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing or empty.
> > > > Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.
> > > > Jul 12 10:00:51 postgresql-check-db-dir: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
> > >
> > > For sure there's all the necessary files in '/data/postgresql/data'.
> > > The startup process cannot read it, because of sym link.
> >
> > Well, where is the source for this fabled "postgresql-check-db-dir"?
>
> https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/9.
> 2/postgresql/EL-7/postgresql92-check-db-dir;h=550b31770cabacf32cbb1b8f
> 272e8ce305fc9908;hb=HEAD

Thanks.  I read this:

  30 # Check for the PGDATA structure
  31 if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
  32 then
     [...]
  49 else
  50     # No existing PGDATA! Warn the user to initdb it.
  51     echo $"\"$PGDATA\" is missing or empty."
  52     echo $"Use \"/usr/pgsql-9.2/bin/postgresql92-setup initdb\" to initialize the database cluster."
  53     echo $"See $PGDOCDIR/README.rpm-dist for more information."
  54     exit 1
  55 fi

That means that either there was no regular file /data/postgresql/data/PG_VERSION or no directory /data/postgresql/data/base, or that the user running the script lacked the permissions to access them.

Since you say that there was a regular data directory there, that would point to permission problems.

Witn that information, it should be simple to debug the problem.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.