psql not working

Cameronnoah · 03-21-2022, 01:07 PM

I downloaded the slack builds for pd, pd-sql and postgres and when I Run psql in the terminal I receive:

Code:

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#

My psql file is as follows:

Code:

bash: /var/lib/pgsql/bin/createdb: No such file or directory
bash-5.1# cd /var
bash-5.1# ls
X11    adm    db     kerberos  lock  mail  named     run    state  www
X11R6  cache  empty  lib       log   man   netatalk  spool  tmp    yp
bash-5.1# 
bash-5.1# cd lib
bash-5.1# ls
AccountsService  elm		   net-snmp   pkgtools	  udisks
NetworkManager	 hp		   nethack    polkit	  udisks2
alsa		 htdig		   nfs	      postfix	  upower
arpd		 iptraf-ng	   ntp	      rootfs-lxc  usb_modeswitch
blueman		 logrotate.status  openldap   rpm	  xdm
bluetooth	 lxc		   openvpn    samba	  xkb
bsdgames	 misc		   os-prober  sddm
dbus		 mlocate	   pgsql      slackpkg
dhcpcd		 mysql		   php	      stunnel
bash-5.1# cd pgsql
bash-5.1# ls
14
bash-5.1# cd 14
bash-5.1# ls
data

Why isn't psql working?

michaelk · 03-21-2022, 01:51 PM

I've never tried installing postgresql on slackware yet but have you read and followed the READMEs?

Before you can start the server you need to create a postgres user and initialize the database as shown in the setup files.

Now you should be able to start the server.

You also need to add your user to postgresql via create user so it can create databases.

https://slackbuilds.org/repository/1...em/postgresql/

All the postgresql utilities should be linked to /usr/bin.

bassmadrigal · 03-21-2022, 01:54 PM

Did you see the README.SBo?

It includes:

Code:

Before you can run postgresql you'll need to create the
database files in /var/lib/pgsql. The following should do
the trick.
	# su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

Cameronnoah · 03-21-2022, 02:12 PM

Quote:

Originally Posted by bassmadrigal

Did you see the README.SBo?

It includes:

Code:

Before you can run postgresql you'll need to create the
database files in /var/lib/pgsql. The following should do
the trick.
	# su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

Code:

bash-5.1$ pg_ctl -D /var/lib/pgsql/14/data -l logfile start
pg_ctl: could not access directory "/var/lib/pgsql/14/data": Permission denied
bash-5.1$ su
Password: 
bash-5.1# pg_ctl -D /var/lib/pgsql/14/data -l logfile start
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
bash-5.1#

khronosschoty · 03-21-2022, 02:14 PM

I can confirm if you follow the instructions properly, that postgresql package works well. I've been using postgresql for years and years on Slackware.

mralk3 · 03-21-2022, 05:25 PM

Quote:

Originally Posted by khronosschoty

I can confirm if you follow the instructions properly, that postgresql package works well. I've been using postgresql for years and years on Slackware.

Its a great database. I use postgresql with all ruby web applications along with Ruby Sinatra and active record gems.

bassmadrigal · 03-21-2022, 07:32 PM

Quote:

Originally Posted by Cameronnoah

Code:

bash-5.1$ pg_ctl -D /var/lib/pgsql/14/data -l logfile start
pg_ctl: could not access directory "/var/lib/pgsql/14/data": Permission denied
bash-5.1$ su
Password: 
bash-5.1# pg_ctl -D /var/lib/pgsql/14/data -l logfile start
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
bash-5.1#

Did you miss the su postgres -c part of the command?

It wants you to run the command as the user postgres, which is done by including the red:

Code:

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

(Don't miss the closing parenthesis

)

Cameronnoah · 03-22-2022, 10:19 AM

Quote:

Originally Posted by bassmadrigal

Did you miss the su postgres -c part of the command?

It wants you to run the command as the user postgres, which is done by including the red:

Code:

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

(Don't miss the closing parenthesis

)

Okay I think I am getting the hang of this. I actually can see and read all of the files that were written in C for nearly all of my programs and psql and that is very interesting. I read the readme and did what it told me to do but now it is asking me for a password. I tried putting in the passwords for both root and the user but it doesn't work. Does psql have a password unto its self and if so how can I figure it out or change it?

michaelk · 03-22-2022, 11:56 AM

What password are you referring to and what command are you trying to run? If your trying to run initdb in your last post there isn't a password. Use su or login as root then run the posted command. Since your already root su will not ask for passwords.

Cameronnoah · 03-22-2022, 12:20 PM

Code:

bash-5.1$ ls
Desktop  Documents  Downloads  Music  Pictures	Public	Templates  Videos
bash-5.1$ cd Documents
bash-5.1$ ls
skeleton
bash-5.1$ cd skeleton
bash-5.1$ ls
Gemfile  Gemfile.lock  data  import_db.sh  lib	spec
bash-5.1$ ./import_db.sh 
Password: 
Password: 
dropdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "ace"
Password:

It is saying the password failed for 'ace' which is my user name. I put both passwords for my root and user ace and it failed. I think this is an psql password. How do I figure that out?

mralk3 · 03-22-2022, 12:23 PM

You need to log in as root on your Slackware system, and then configure the database and its permissions.

michaelk · 03-22-2022, 12:29 PM

postgresql passwords are different from user passwords. You need to add the password to your user.

As the postgres user run the command from psql.

ALTER ROLE ace WITH PASSWORD 'my_password';

Cameronnoah · 03-22-2022, 01:50 PM

Quote:

Originally Posted by michaelk

postgresql passwords are different from user passwords. You need to add the password to your user.

As the postgres user run the command from psql.

ALTER ROLE ace WITH PASSWORD 'my_password';

Okay I Need to run the command from psql? That is I need to access the psql terminal which requires a password?

bassmadrigal · 03-22-2022, 02:52 PM

Quote:

Originally Posted by Cameronnoah

Okay I Need to run the command from psql? That is I need to access the psql terminal which requires a password?

The system is asking for the password of postgres, which should not have a password set (this is normal). This increases security and requires that you run the su command as root (as root does not need passwords to run as other users).

So, you should be able to get the initial database set up by doing the following:

Code:

su -
<enter root's password>
su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

In reference to the README.SBo, if you see commands to run preceded by a "#", that is indicating to run the command as root. If you see it preceded by a "$", that indicates you can run it as a normal user. In reference to the command, notice the section I colored red. That is showing to run the command as root.

Code:

Before you can run postgresql you'll need to create the
database files in /var/lib/pgsql. The following should do
the trick.
	# su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

Cameronnoah · 03-22-2022, 03:00 PM

Quote:

Originally Posted by bassmadrigal

The system is asking for the password of postgres, which should not have a password set (this is normal). This increases security and requires that you run the su command as root (as root does not need passwords to run as other users).

So, you should be able to get the initial database set up by doing the following:

Code:

su -
<enter root's password>
su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

In reference to the README.SBo, if you see commands to run preceded by a "#", that is indicating to run the command as root. If you see it preceded by a "$", that indicates you can run it as a normal user. In reference to the command, notice the section I colored red. That is showing to run the command as root.

Code:

Before you can run postgresql you'll need to create the
database files in /var/lib/pgsql. The following should do
the trick.
	# su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"

Okay will do. I get the following:

Code:

bash-5.1$ su
Password:
bash-5.1# su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
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 locale "en_US.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.

Enter new superuser password:
Enter it again:

initdb: error: directory "/var/lib/pgsql/14/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/pgsql/14/data" or run initdb
with an argument other than "/var/lib/pgsql/14/data".
bash-5.1# su postgres -c "initdb -D /var/lib/pgsql/14/data --locale=en_US.UTF-8 -A md5 -W"
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 locale "en_US.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.

Enter new superuser password:
Enter it again:

initdb: error: directory "/var/lib/pgsql/14/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/pgsql/14/data" or run initdb
with an argument other than "/var/lib/pgsql/14/data".
bash-5.1# initb
bash: initb: 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# initdb /var/lib/pgsql/14/data2
initdb: error: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
bash-5.1# su initdb /var/lib/pgsql/14/data2
No passwd entry for user 'initdb'
bash-5.1#

What does this mean?

Then I ran the following:

Code:

bash-5.1# su postgres -c "initdb -D /var/lib/pgsql/14/data2 --locale=en_US.UTF-8 -A md5 -W"
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 locale "en_US.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.

Enter new superuser password: 
Enter it again: 

creating directory /var/lib/pgsql/14/data2 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/14/data2 -l logfile start

bash-5.1#

Then I looked if I could run the script:

Code:

bash-5.1$ ls
Desktop  Documents  Downloads  Music  Pictures	Public	Templates  Videos
bash-5.1$ cd Documents
bash-5.1$ l
bash: l: command not found
bash-5.1$ ls
skeleton
bash-5.1$ cd skeleton/
bash-5.1$ ls
Gemfile  Gemfile.lock  data  import_db.sh  lib	spec
bash-5.1$ pg_ctl -D /var/lib/pgsql/14/data2 -l logfile start
pg_ctl: could not access directory "/var/lib/pgsql/14/data2": Permission denied
bash-5.1$ sudo pg_ctl -D /var/lib/pgsql/14/data2 -l logfile start
Password: 
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
bash-5.1$ ./import_db.sh 
Password: 
Password: 
dropdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "ace"
Password:

The scrip merely creates a db:

#!/usr/bin/env sh

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