psql not working

bassmadrigal · 03-22-2022, 03:44 PM

I am not a postgresql user, but it seems that somehow you already had a database created in /var/lib/pgsql/14/data. You were able to avoid that error by changing it to /var/lib/pgsql/14/data2. If you don't want to have to remember you're using a different database, you can delete both of the folders and then rerun the initial command again.

To start that command, you'd need to run it as the postgres user again. It would be within the quotes of that su postgres command you used earlier.

Code:

su postgres -c "pg_ctl -D /var/lib/pgsql/14/data2 -l logfile start"

However, the maintainer of postgresql has made an rc script for it, which needs to be made executable and then executed with "start".

Code:

su -
chmod +x /etc/rc.d/rc.postgresql
sh /etc/rc.d/rc.postgresql start

However, if you decided to keep the new /var/lib/pgsql/14/data2 database, you will need to edit rc.postgresql to change the following line:

Code:

DATADIR=/var/lib/pgsql/14/data

To:

Code:

DATADIR=/var/lib/pgsql/14/data2

Cameronnoah · 03-24-2022, 03:20 PM

Quote:

Originally Posted by bassmadrigal

I am not a postgresql user, but it seems that somehow you already had a database created in /var/lib/pgsql/14/data. You were able to avoid that error by changing it to /var/lib/pgsql/14/data2. If you don't want to have to remember you're using a different database, you can delete both of the folders and then rerun the initial command again.

To start that command, you'd need to run it as the postgres user again. It would be within the quotes of that su postgres command you used earlier.

Code:

su postgres -c "pg_ctl -D /var/lib/pgsql/14/data2 -l logfile start"

However, the maintainer of postgresql has made an rc script for it, which needs to be made executable and then executed with "start".

Code:

su -
chmod +x /etc/rc.d/rc.postgresql
sh /etc/rc.d/rc.postgresql start

However, if you decided to keep the new /var/lib/pgsql/14/data2 database, you will need to edit rc.postgresql to change the following line:

Code:

DATADIR=/var/lib/pgsql/14/data

To:

Code:

DATADIR=/var/lib/pgsql/14/data2

root@Bern:~# su postgres -c "pg_ctl -D /var/lib/pgsql/14/data2 -l logfile start"
could not change directory to "/root": Permission denied
waiting for server to start..../bin/sh: line 1: logfile: Permission denied
stopped waiting
pg_ctl: could not start server
Examine the log output.
root@Bern:~#

I RECEIVE THE ABOVE.

khronosschoty · 03-24-2022, 03:29 PM

If it were me, If haven't used it for anything important yet, and not afraid of loosing any data. I might just nuke it all, use removepkg and then rm -rf /var/lib/pgsql and then reinstall and go through the instructions carefully in the README etc.

bassmadrigal · 03-24-2022, 04:07 PM

Quote:

Originally Posted by Cameronnoah

root@Bern:~# su postgres -c "pg_ctl -D /var/lib/pgsql/14/data2 -l logfile start"
could not change directory to "/root": Permission denied
waiting for server to start..../bin/sh: line 1: logfile: Permission denied
stopped waiting
pg_ctl: could not start server
Examine the log output.
root@Bern:~#

I RECEIVE THE ABOVE.

Based on your output, I'm guessing the -l option is the literal location of the logfile, which postgres won't have access to /root/ (as you're executing this in root's home directory). Until you get permissions set up for how you want logs done, you could change it to something like:

Code:

su postgres -c "pg_ctl -D /var/lib/pgsql/14/data2 -l /tmp/postgresql.log start"

However, ideally, you should be able to just run /etc/rc.d/rc.postgresql start as root if the maintainer has it set up to run without modification (looking over it on SBo, it seems like it should be ready to just run).

If it works fine, then you should just pull the portion from the README.SBo and add that into your /etc/rc.d/rc.local to have it start automatically on boot.

Cameronnoah · 03-25-2022, 07:46 AM

Code:

Executing install script for postgresql-14.2-x86_64-1_SBo.tgz.
Package postgresql-14.2-x86_64-1_SBo.tgz installed.
bash-5.1# su
bash-5.1# postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.

bash-5.1$ postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
2022-03-25 12:44:28.807 GMT [19030] FATAL:  unrecognized configuration parameter "initdb _D /var/lib/pgsql/14/data __locale"
bash-5.1$ cd /var/lib
bash-5.1$ ls
AccountsService  bsdgames  iptraf-ng         net-snmp  os-prober  rootfs-lxc  stunnel         xkb
NetworkManager   dbus      logrotate.status  nethack   pgsql      rpm         udisks
alsa             dhcpcd    lxc               nfs       php        samba       udisks2
arpd             elm       misc              ntp       pkgtools   sbopkg      upower
blueman          hp        mlocate           openldap  polkit     sddm        usb_modeswitch
bluetooth        htdig     mysql             openvpn   postfix    slackpkg    xdm
bash-5.1$

I reinstalled postgressql and ran the above commands and it appears I can not start or run the data base in the 14 file of the pqsql folder.

Cameronnoah · 03-25-2022, 08:53 AM

Code:

GNU nano 6.0                                         pg_hba.conf                                                    
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the server receives a
# SIGHUP signal.  If you edit the file on a running system, you have to
# SIGHUP the server for the changes to take effect, run "pg_ctl reload",
# or execute "SELECT pg_reload_conf()".
#
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

This is my pg_hba.conf file. I assume I need to address the METHOD md5 portion so I don't need a password to create a psql data base or at least get it STARTED!

khronosschoty · 03-25-2022, 10:38 AM

Quote:

Originally Posted by Cameronnoah

Code:

Executing install script for postgresql-14.2-x86_64-1_SBo.tgz.
Package postgresql-14.2-x86_64-1_SBo.tgz installed.
bash-5.1# su
bash-5.1# postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.

bash-5.1$ postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
2022-03-25 12:44:28.807 GMT [19030] FATAL:  unrecognized configuration parameter "initdb _D /var/lib/pgsql/14/data __locale"
bash-5.1$ cd /var/lib
bash-5.1$ ls
AccountsService  bsdgames  iptraf-ng         net-snmp  os-prober  rootfs-lxc  stunnel         xkb
NetworkManager   dbus      logrotate.status  nethack   pgsql      rpm         udisks
alsa             dhcpcd    lxc               nfs       php        samba       udisks2
arpd             elm       misc              ntp       pkgtools   sbopkg      upower
blueman          hp        mlocate           openldap  polkit     sddm        usb_modeswitch
bluetooth        htdig     mysql             openvpn   postfix    slackpkg    xdm
bash-5.1$

I reinstalled postgressql and ran the above commands and it appears I can not start or run the data base in the 14 file of the pqsql folder.

Code:

su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

Should be one line, it looks like from what you copied and pasted you have su on one line and then the rest of the command on anther line. I'm not sure if that is just a formatting issue on your part, but it really doesn't look like that is the case. Also the error message you report seems to support the idea that your not giving the command on one line, and as such not running the command as the right user etc.

Cameronnoah · 03-27-2022, 09:50 AM

Quote:

Originally Posted by khronosschoty

Code:

su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

Should be one line, it looks like from what you copied and pasted you have su on one line and then the rest of the command on anther line. I'm not sure if that is just a formatting issue on your part, but it really doesn't look like that is the case. Also the error message you report seems to support the idea that your not giving the command on one line, and as such not running the command as the right user etc.

I ran it on one file. I tried installing it via the source code and it was very complex so I went back to installing it via the Slackbuild. All is good until I need to run:

Code:

su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

This is what I get when I try to run the above command in multiple manners. It does not execute and I believe this is why Postgres is not working. I decided to ride this one out and learn how to use Slackware, Linux, PSQL and Full Stack so as to maximize my knowledge base and contribute to the Slackware project. I noticed while installing postgres via this blog that certain important yet archiac programs aren't in the Sbo website which I plan on adding. This guy has some interesting blogs on Slackware: http://www.pmoghadam.com/homepage/HT...esql-epkg.html
Below is my attempts to execute the commands, what do you think is going on?

Code:

bash-5.1$ su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
Password: 
su: Authentication failure
bash-5.1$ sudo postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.
bash-5.1$ su - postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
Password: 
su: Authentication failure
bash-5.1$ su - postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
Password: 
su: Authentication failure
bash-5.1$ su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
Password: 
su: Authentication failure
bash-5.1$ su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
Password: 
su: Authentication failure
bash-5.1$ su postgres -c init -D /var/lib/pgsql/14/data --locale-en_US.UTF-8 -A md5 -W
su: invalid option -- 'D'
Usage: su [options] [-] [username [args]]

Options:
  -c, --command COMMAND         pass COMMAND to the invoked shell
  -h, --help                    display this help message and exit
  -, -l, --login                make the shell a login shell
  -m, -p,
  --preserve-environment        do not reset environment variables, and
                                keep the same shell
  -s, --shell SHELL             use SHELL instead of the default in passwd

If no username is given, assume root.

Code:

bash-5.1$ su
Password: 
bash-5.1# postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.
bash-5.1# su - postgres

We are upping our standards ... so up yours.
                -- Pat Paulsen for President, 1988

postgres@Bern:~$ postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
2022-03-27 14:57:17.257 GMT [32152] FATAL:  unrecognized configuration parameter "initdb _D /var/lib/pgsql/14/data __locale"
postgres@Bern:~$ postgres -c initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W
2022-03-27 14:57:30.413 GMT [32155] FATAL:  -c initdb requires a value
postgres@Bern:~$

I don't know why this is not working given the fact I listened to and followed the holy read me. I trust in the Linux way and more importantly Slackware as a deity and magical piece of software. I will continue to search for results.

Petri Kaukasoina · 03-27-2022, 10:02 AM

It seems that you do not read the answers you get from experts (or error messages from the commands). Read very carefully this post where bassmadrigal tells you to first give 'su -' to become root and after that give the command to start something as the unprivileged user postgres. Time after time you fail in this.

You can copy and paste it from bassmadrigal's post, so you don't leave out important parts of the command lines!

Cameronnoah · 03-27-2022, 10:07 AM

I know I have to create a data base and an account called postgres in order to use postgres and psql. What exactly is the point of creating a user in Linux called postgres and what role does it play in psql and its server? Why can't I use psql as my current user or root? Why do I need to create a user postgres in Linux, is there a postgres user account "in postgres/psql" and if so what relationship does it play to building, creating or making a new data base?

Cameronnoah · 03-27-2022, 10:25 AM

Quote:

Originally Posted by Petri Kaukasoina

It seems that you do not read the answers you get from experts (or error messages from the commands). Read very carefully this post where bassmadrigal tells you to first give 'su -' to become root and after that give the command to start something as the unprivileged user postgres. Time after time you fail in this.

You can copy and paste it from bassmadrigal's post, so you don't leave out important parts of the command lines!

Okay, I did that I am sorry for not reading the comments, a lot is going on. I did what you told me to do. Now I need to run:

Code:

 GNU nano 6.0                      import_db.sh                                
#!/usr/bin/env sh

dropdb sqlzoo
createdb sqlzoo
psql sqlzoo < data/create_tables.sql
ls

In the directory of a project file that needs to use psql. I receive the following errors:

Code:

bash-5.1# cd skeleton
bash-5.1# ls
Gemfile  Gemfile.lock  data  database  import_db.sh  lib  logfile  spec
bash-5.1# ./import_db.sh 
dropdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
Gemfile  Gemfile.lock  data  database  import_db.sh  lib  logfile  spec
bash-5.1# startdb
bash: startdb: command not found
bash-5.1# initdb
initdb: error: no data directory specified
You must identify the directory where the data for this database system
will reside.  Do this with either the invocation option -D or the
environment variable PGDATA.
bash-5.1# psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
bash-5.1# 
bash-5.1# nano import_db.sh

Why is this error occurring? Why can't I use psql?

This problem is almost solved! Still working at it, day and night!

Please bare with me, I am learning a shit ton and remember it is MORE fun to compute https://www.youtube.com/watch?v=2zmt...&start_radio=1

Cameronnoah · 03-27-2022, 10:33 AM

Like honestly what is socket "/tmp/.s.PGSQL.5432" failed: No such file or directory and how can I address. I just want to learn some thing tbh atm and not even code. Like I am genuinely curious. What is going on and how is that specific.

Petri Kaukasoina · 03-27-2022, 10:47 AM

The commands give error messages asking you whether the server is running. In this post bassmadrigal told you to start it with 'sh /etc/rc.d/rc.postgresql start'. Did you start it?

(I know nothing about postgres but this is how you start services in Slackware.)

Cameronnoah · 03-27-2022, 10:53 AM

Quote:

Originally Posted by Petri Kaukasoina

The commands give error messages asking you whether the server is running. In this post bassmadrigal told you to start it with 'sh /etc/rc.d/rc.postgresql start'. Did you start it?

(I know nothing about postgres but this is how you start services in Slackware.)

I did that and received:

Code:

bash-5.1$ su
Password: 
bash-5.1# sh /etc/rc.d/rc.postgresql start
Starting PostgreSQL
pg_ctl: could not open PID file "/var/lib/pgsql/14/data2/postmaster.pid": Permission denied

Petri Kaukasoina · 03-27-2022, 11:34 AM

Quote:

Originally Posted by Cameronnoah

pg_ctl: could not open PID file "/var/lib/pgsql/14/data2/postmaster.pid": Permission denied

So did you put the database in /var/lib/pgsql/14/data or /var/lib/pgsql/14/data2 ? The package has made the directory /var/lib/pgsql/14/data for you with the correct owner, group, and access bits. Maybe you really use /var/lib/pgsql/14/data but forgot you edited DATADIR in /etc/rc.d/rc.postgresql ?

Either you don't have directory /var/lib/pgsql/14/data2 at all or you have it but with incorrect owner, so that pg_ctl can't write to it.

What does

Code:

ls -la /var/lib/pgsql/14/data2

show?
And then

Code:

ls -la /var/lib/pgsql/14/data