Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target

El día Wednesday, September 18, 2019 a las 11:50:45AM +0200, Marco Ippolito escribió:

I installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition using package
manager: sudo apt-get install postgresql-11. But now I'm not able to call
psql:

(base) marco@pc:~$ sudo su -l postgres
[sudo] password for marco:
No directory, logging in with HOME=/
postgres@pc:/$ psql
Warning: No existing local cluster is suitable as a default target.
Please
see man pg_wrapper(1) how to specify one.
Error: You must install at least one postgresql-client-<version> package
postgres@pc:/$

I tried to specify the port to use but it asks for a postgresql-client:

(base) marco@pc:~$ sudo -u postgres /usr/bin/psql -p 5432
Error: You must install at least one postgresql-client-<version> package

But the latest postgresql-client is already installed:

(base) marco@pc:~$ sudo apt-get install postgresql-client-11
Reading package lists... Done
Building dependency tree
Reading state information... Done
postgresql-client-11 is already the newest version (11.5-1.pgdg18.04+1).
0 to upgrade, 0 to newly install, 0 to remove and 0 not to upgrade.

How to solve the problem? Looking forward to your kind help.
Marco

Have you setup and started any PG cluster? See
https://www.postgresql.org/docs/11/runtime.html
(chapter 18. Server Setup and Operation).

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Thanks Matthias.

Followed these steps (indicated here:
https://www.postgresql.org/docs/11/creating-cluster.html )

root# *chown postgres /usr/local/pgsql*
root# *su postgres*
postgres$ *initdb -D /usr/local/pgsql/data*

postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/initdb -D
/usr/local/pgsql/data
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locales
COLLATE: en_GB.UTF-8
CTYPE: en_GB.UTF-8
MESSAGES: en_GB.UTF-8
MONETARY: C.UTF-8
NUMERIC: C.UTF-8
TIME: C.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Europe/Rome
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile
start

But now permission denied:

postgres@pc:/home/marco$ sudo /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l logfile start
[sudo] password for postgres:
postgres is not in the sudoers file. This incident will be reported.
postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l logfile start
waiting for server to start..../bin/sh: 1: cannot create logfile:
Permission denied
stopped waiting
pg_ctl: could not start server
Examine the log output.
postgres@pc:/home/marco$

Il giorno mer 18 set 2019 alle ore 12:03 Matthias Apitz <guru@unixarea.de>
ha scritto:

På onsdag 18. september 2019 kl. 12:13:24, skrev Marco Ippolito <
ippolito.marco@gmail.com <mailto:ippolito.marco@gmail.com>>: Thanks Matthias.
Followed these steps (indicated here:
https://www.postgresql.org/docs/11/creating-cluster.html
<https://www.postgresql.org/docs/11/creating-cluster.html&gt; ) root# chown
postgres /usr/local/pgsql root# su postgres postgres$ initdb -D
/usr/local/pgsql/data postgres@pc:/home/marco$
/usr/lib/postgresql/11/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
COLLATE: en_GB.UTF-8
CTYPE: en_GB.UTF-8
MESSAGES: en_GB.UTF-8
MONETARY: C.UTF-8
NUMERIC: C.UTF-8
TIME: C.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Europe/Rome
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
But now permission denied: postgres@pc:/home/marco$ sudo
/usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
[sudo] password for postgres:
postgres is not in the sudoers file. This incident will be reported.
postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l logfile start
waiting for server to start..../bin/sh: 1: cannot create logfile: Permission
denied
stopped waiting
pg_ctl: could not start server
Examine the log output.
postgres@pc:/home/marco$ Start pg_ctl as postgres user, no need to sudo. --
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com&gt; <https://www.visena.com&gt;

Hi Andreas,
if I understand correctly, this is what I've done afterwards:

postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l logfile start
waiting for server to start..../bin/sh: 1: cannot create logfile:
Permission denied
stopped waiting
pg_ctl: could not start server
Examine the log output.
postgres@pc:/home/marco$

What am I doing wrong?

Il giorno mer 18 set 2019 alle ore 12:22 Andreas Joseph Krogh <
andreas@visena.com> ha scritto:

På onsdag 18. september 2019 kl. 12:25:05, skrev Marco Ippolito <
ippolito.marco@gmail.com <mailto:ippolito.marco@gmail.com>>: Hi Andreas, if I
understand correctly, this is what I've done afterwards:
postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l logfile start
waiting for server to start..../bin/sh: 1: cannot create logfile: Permission
denied
stopped waiting
pg_ctl: could not start server
Examine the log output.
postgres@pc:/home/marco$ What am I doing wrong? You don't have permissions to
create the logfile (named "logfile" in your command) in CWD (/home/macro).
Specify absolute path to somewhere writable for user "postgres". -- Andreas
Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

thanks Andreas.

After changing ownership of /usr/lib/postgresql to postgres user,

postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l /usr/lib/postgresql/11/log/logfile start
waiting for server to start.... done
server started
postgres@pc:/home/marco$

But now... how to call and use psql?

postgres@pc:/home/marco$ psql
Warning: No existing local cluster is suitable as a default target. Please
see man pg_wrapper(1) how to specify one.
Error: You must install at least one postgresql-client-<version> package
postgres@pc:/home/marco$

I guess I'm still missing something...

Il giorno mer 18 set 2019 alle ore 12:28 Andreas Joseph Krogh <
andreas@visena.com> ha scritto:

El día Wednesday, September 18, 2019 a las 12:44:20PM +0200, Marco Ippolito escribió:

thanks Andreas.

After changing ownership of /usr/lib/postgresql to postgres user,

postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l /usr/lib/postgresql/11/log/logfile start
waiting for server to start.... done
server started
postgres@pc:/home/marco$

But now... how to call and use psql?

Marco, I compiled 11.4 on Linux for our needs and wrote a Confluence
page about this and the cluster setup. I could send you this pages in PDF off-list.

matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Thanks Matthias,
very kind. Hopefully I will get some hints on how to solve my problem.

Marco

Il giorno mer 18 set 2019 alle ore 15:11 Matthias Apitz <guru@unixarea.de>
ha scritto:

On 9/18/19 6:13 AM, Marco Ippolito wrote:

Thanks Matthias,
very kind. Hopefully I will get some hints on how to solve my problem.

You should probably take a look at:

https://help.ubuntu.com/lts/serverguide/postgresql.html

FYI, you should set the permission son /usr/lib/postgresql back to root
and use:

sudo pg_ctl <command>

Marco

Il giorno mer 18 set 2019 alle ore 15:11 Matthias Apitz
<guru@unixarea.de <mailto:guru@unixarea.de>> ha scritto:

El día Wednesday, September 18, 2019 a las 12:44:20PM +0200, Marco
Ippolito escribió:

thanks Andreas.

After changing ownership of /usr/lib/postgresql to postgres user,

postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
/usr/local/pgsql/data -l /usr/lib/postgresql/11/log/logfile start
waiting for server to start.... done
server started
postgres@pc:/home/marco$

But now... how to call and use psql?

Marco, I compiled 11.4 on Linux for our needs and wrote a Confluence
page about this and the cluster setup. I could send you this pages
in PDF off-list.

        matthias
--
Matthias Apitz, ✉ guru@unixarea.de <mailto:guru@unixarea.de>,
http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

after purging from Ubuntu 18.04.02 everything related to postgresql-11, I
re-installed it and made it working, yes...finally working... with this
useful and good guidelines:
https://pgdash.io/blog/postgres-11-getting-started.html.

Marco

Il giorno mer 18 set 2019 alle ore 16:41 Adrian Klaver <
adrian.klaver@aklaver.com> ha scritto:

El día miércoles, septiembre 18, 2019 a las 07:41:41a. m. -0700, Adrian Klaver escribió:

On 9/18/19 6:13 AM, Marco Ippolito wrote:

Thanks Matthias,
very kind. Hopefully I will get some hints on how to solve my problem.

You should probably take a look at:

https://help.ubuntu.com/lts/serverguide/postgresql.html

FYI, you should set the permission son /usr/lib/postgresql back to root
and use:

sudo pg_ctl <command>

NAK. In my understanding, the dir above the data dir of the cluster
should be owned by 'postgres' and the pg_ctl run as 'postgres':

# mkdir /data/postgresql11
# chown postgres /data/postgresql11
# su - postgres
$ LANG=de_DE.UTF-8 export LANG
$ /usr/local/sisis-pap/pgsql/bin/pg_ctl -D /data/postgresql11/data initdb

This is stolen from the pages mentioned, i.e. https://www.postgresql.org/docs/11/runtime.html
(chapter 18. Server Setup and Operation) and worked out of the box fine
for me.

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Mientras haya voluntad de lucha habrá esperanza de vencer.

On 9/18/19 7:49 AM, Matthias Apitz wrote:

El día miércoles, septiembre 18, 2019 a las 07:41:41a. m. -0700, Adrian Klaver escribió:

On 9/18/19 6:13 AM, Marco Ippolito wrote:

Thanks Matthias,
very kind. Hopefully I will get some hints on how to solve my problem.

You should probably take a look at:

https://help.ubuntu.com/lts/serverguide/postgresql.html

FYI, you should set the permission son /usr/lib/postgresql back to root
and use:

sudo pg_ctl <command>

NAK. In my understanding, the dir above the data dir of the cluster
should be owned by 'postgres' and the pg_ctl run as 'postgres':

# mkdir /data/postgresql11
# chown postgres /data/postgresql11
# su - postgres
$ LANG=de_DE.UTF-8 export LANG
$ /usr/local/sisis-pap/pgsql/bin/pg_ctl -D /data/postgresql11/data initdb

This is stolen from the pages mentioned, i.e. https://www.postgresql.org/docs/11/runtime.html
(chapter 18. Server Setup and Operation) and worked out of the box fine
for me.

Its a recommendation and not what the Debian/Ubuntu packaging does.
From a Ubuntu 16.04.6 LTS install:

aklaver@arkansas:~$ ls -al /usr/lib/postgresql/
total 24
drwxr-xr-x 4 root root 4096 May 24 09:57 .
drwxr-xr-x 49 root root 12288 Mar 5 2019 ..
drwxr-xr-x 4 root root 4096 Mar 17 2018 10
drwxr-xr-x 4 root root 4096 Oct 20 2018 11

aklaver@arkansas:~$ l /var/lib/postgresql/
total 32
drwxr-xr-x 6 postgres postgres 4096 May 29 13:26 ./
drwxr-xr-x 41 root root 4096 May 24 09:47 ../
drwxr-xr-x 3 postgres postgres 4096 Mar 17 2018 10/
drwxr-xr-x 3 postgres postgres 4096 Oct 20 2018 11/
-rw------- 1 postgres postgres 120 May 24 15:07 .bash_history
drwx------ 2 postgres postgres 4096 May 24 15:07 .cache/
-rw-rw-r-- 1 postgres postgres 0 May 29 13:27 cat
-rw------- 1 postgres postgres 90 Feb 11 2017 .psql_histor

From local source install:

postgres@tito:~> l /usr/local/pgsql11
total 28
drwxr-xr-x 7 root root 4096 Jan 4 2019 ./
drwxr-xr-x 18 root root 4096 Jul 6 13:54 ../
drwxr-xr-x 2 root root 4096 Aug 26 17:41 bin/
drwx------ 20 postgres postgres 4096 Sep 18 06:27 data/
drwxr-xr-x 6 root root 4096 Aug 26 17:41 include/
drwxr-xr-x 4 root root 4096 Sep 11 07:38 lib64/
drwxr-xr-x 6 root root 4096 Aug 26 17:41 share/

As long as the data directory is owned by postgres all is good.

matthias

--
Adrian Klaver
adrian.klaver@aklaver.com