Postgresql Server

I have tried to get the postgresql (installed versions. 9.6-1.1-noarch & 9.6.2-1.1 with server and contribution packages) running without success, Maybe I have messed up somehow with the settings,
I have done the following changes in /var/lib/pgsql/data/ original files
postgresql,conf file:
hba_file = ‘/var/lib/pgsql/data/pg_hba.conf’
listen-address ‘*’
port = 5432
unix_socket_permissions = 0777

pg_hba.conf file:
TYPE DATABASE USER ADDRESS METHOD
local all all trust (tried also with md5)
host all all 127.0.0.1/32 ident
host all all ::1/128 ident

When opening in terminal, I get:

~> su - postgres
Password:
su: Authentication failure

and as super user I get:

sudo su - postgres
[sudo] password for root:
postgres@LAPTOP-…:~> psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?

With the command:~> sudo systemctl status postgresql.service
I get:

[sudo] password for root:
postgresql.service - LSB: Start the PostgreSQL master daemon
Loaded: loaded (/etc/init.d/postgresql; generated; vendor preset: disabled)
Active: failed (Result: exit-code) since Mon 2017-03-20 13:56:40 EET; 1h 44min ago
Docs: man:systemd-sysv-generator(8)
Process: 5963 ExecStart=/etc/init.d/postgresql start (code=exited, status=1/FAILURE)

Mar 20 13:56:39 LAPTOP-5HS2NJ5L.home su[5975]: pam_unix(su-l:session): session opened fo
Mar 20 13:56:39 LAPTOP-5HS2NJ5L.home su[6009]: (to postgres) root on none
Mar 20 13:56:39 LAPTOP-5HS2NJ5L.home su[6009]: pam_unix(su-l:session): session opened fo
Mar 20 13:56:40 LAPTOP-5HS2NJ5L.home postgresql[5963]: Starting PostgreSQL 9.6.2 pg_ctl:
Mar 20 13:56:40 LAPTOP-5HS2NJ5L.home postgresql[5963]: Examine the log output.
Mar 20 13:56:40 LAPTOP-5HS2NJ5L.home postgresql[5963]: …failed
Mar 20 13:56:40 LAPTOP-5HS2NJ5L.home systemd[1]: postgresql.service: Control process exi
Mar 20 13:56:40 LAPTOP-5HS2NJ5L.home systemd[1]: Failed to start LSB: Start the PostgreS
Mar 20 13:56:40 LAPTOP-5HS2NJ5L.home systemd[1]: postgresql.service: Unit entered failed
Mar 20 13:56:40 LAPTOP-5HS2NJ5L.home systemd[1]: **postgresql.service: Failed with result
**----

What is missing to get it running?

Did you test your PostgreSQL when it was initially installed?
Typically, everything installed from the openSUSE repos will be installed with a reasonable working configuration by default, generally with default values (Unlike some other distros).

So, instead of mucking around with configuration files, you should probably for instance have followed the simple steps in this SDB:PostgeSQL, although it’s 2+ years old the simple steps described are still completely applicable today…
https://en.opensuse.org/SDB:PostgreSQL

BTW - The article I found at postgresql.org on openSUSE contains some really weird stuff, and shouldn’t be used.

TSU

ThanksTsu,
I tried to follow these (https://www.howtoforge.com/tutorial/how-to-install-postgresql-and-phppgadmin-on-opensuse-leap-42.1/), (https://wiki.postgresql.org/wiki/Zypper_Installation).and (https://www.postgresql.org/docs/9.6/static/config-setting.html).
Nothing works!
Btw, could not find the postgreSQL instructions you referred to. Any other advice or should I just remove everything insalled with Yast?

Did you check the server log file to see why the start fails?

Depending on your method of installation you should find it either in /var/lib/pgsql/data/ or in the subdirectory pg_log.

If there is no valuable data in your server it might be a good idea, to remove everything and start from scratch following the instructions on the site you find with tsu2’s link.

https://en.opensuse.org/SDB:PostgreSQL

And please, use the version from the openSUSE standard repository and not from any of the repos in the article “https://wiki.postgresql.org/wiki/Zypper_Installation”.
Hendrik

Yes,
As I mentioned in my prior post, at the moment any documentation relating to SUSE/openSUSE should not be used. And, the specific article referenced in particular is absolutely weird, the instructions and commands are not in any way usual.

As for the HowtoForge article,
Although I’ve found many articles objectionable on that site, the one you reference seems to be problem-free to my eye.
If you did follow that article, until what point were you successful until you ran into a problem?

In any case,
Not being able to start the PostgreSQL app/service is a show-stopper, and needs to be fixed.
You can try to undo what you’ve done but often that’s not realistic.
If necessary, wipe everything clean.

If this is your first attempt at installing and installing PostreSQL, I’d strongly recommend you install in a virtual machine for practice and learning, because it allows you to quickly wipe clean and start over quickly (iBy either cloning your virtual machine or implemented snapshots). Virtual machines can be easily rolled back or disposed quickly and replaced. If you’re trying to learn by installing apps directly on your bare metal installed OS, there will be times when you muck things up so badly that it’ll take you more time to undo things than actually learn how to install and configure.

TSU

Thanks Hendrik and Tsu,

If I remember it correctly, the problems started after creation of a new user/password and a new db (which I could not find anywhere) in accordance with ‘https://www.howtoforge.com/tutorial/how-to-install-postgresql-and-phppgadmin-on-opensuse-leap-42.1/’. At that time I had not modified the postgresql.conf nor pg_hba,conf file.
The pg_log is as follows:

2017-03-19 17:59:31 EET LOG: database system was shut down at 2017-03-19 17:59:27 EET2017-03-19 17:59:31 EET LOG: MultiXact member wraparound protections are now enabled

2017-03-19 17:59:31 EET LOG: database system is ready to accept connections
2017-03-19 17:59:31 EET LOG: autovacuum launcher started
2017-03-19 18:36:52 EET LOG: received fast shutdown request
2017-03-19 18:36:52 EET LOG: aborting any active transactions
2017-03-19 18:36:52 EET LOG: autovacuum launcher shutting down
2017-03-19 18:36:52 EET LOG: shutting down
2017-03-19 18:36:52 EET LOG: database system is shut down

The postgresql.service file (of attemps to connect to server 3/20/17 9:21 PM) in

/run/systemd/generator.late/postgresql.service:

[Unit]Documentation=man:systemd-sysv-generator(8)

SourcePath=/etc/init.d/postgresql
Description=LSB: Start the PostgreSQL master daemon
Before=multi-user.target
Before=graphical.target
After=network-online.target
After=remote-fs.target
Wants=network-online.target
Wants=remote-fs.target

[Service]
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
SuccessExitStatus=5 6
ExecStart=/etc/init.d/postgresql start
ExecStop=/etc/init.d/postgresql stop
ExecReload=/etc/init.d/postgresql reload

This is no catasthroph for me. Would be nice to know, how to fix this problem.

My latest try with pg_hba.conf settings was:

local all all trust

host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host postgres all 192.168.12.0/24 trust

No help!

Could it be so that the definitions in /etc/sysconfig/postgresql are not correctly adjusted/are missing (like ## Path: Applications/PostgreSQL, POSTGRES_DATADIR=“~postgres/data”, POSTGRES_OPTIONS=“”)? Or are my user definitions somehow corrupted?

@SimoKJ

I have send you a PM about the usage of CODE tags. Did you not receive it?

Yes, thank you. Got it.

Hi,

first you should check if postgres is running, for example with:

systemctl status postgresql.service

If it is not running start it with.

systemctl start postgresql.service

If you check it afterwards, you should see something like:

● postgresql.service - LSB: Start the PostgreSQL master daemon
   Loaded: loaded (/etc/init.d/postgresql; generated; vendor preset: disabled)
   Active: active (exited) since Tue 2017-03-21 20:07:06 CET; 1s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 29691 ExecStart=/etc/init.d/postgresql start (code=exited, status=0/SUCCESS)

Mär 21 20:07:01 schlepptop.site systemd[1]: Starting LSB: Start the PostgreSQL master daemon...
Mär 21 20:07:01 schlepptop.site su[29703]: (to postgres) root on none
Mär 21 20:07:01 schlepptop.site su[29703]: pam_unix(su-l:session): session opened for user postgres by (uid=0)
Mär 21 20:07:03 schlepptop.site su[29740]: (to postgres) root on none
Mär 21 20:07:03 schlepptop.site su[29740]: pam_unix(su-l:session): session opened for user postgres by (uid=0)
Mär 21 20:07:06 schlepptop.site postgresql[29691]: Starting PostgreSQL 9.6.2 ..done
Mär 21 20:07:06 schlepptop.site systemd[1]: Started LSB: Start the PostgreSQL master daemon.

Then you can try to connect to the database.

Some more things to note:

  • Do not change paths etc. in /etc/sysconfig/postgresql after you have started the postgres server with the systemctl command once.
  • This command invokes another script, which checks, that a postgres database server installation exists in the default location and creates one, if it doesn’t find one there.
  • So if you change the paths, the script will create a new server instance for you and you will miss anything you did in your former server instance.

Hendrik

It’s hard to believe that changing a password or creating a database could cause the application service to fail.

IMO it won’t be likely that you can remember or know what you did that caused the failure you’re experiencing.

I recommend you abandon what you did,
Install Virtualbox or VMware Player, or KVM
Create a new openSUSE Guest with PostgreSQL installed and proceed.
As I described earlier, you can create a “Golden Image” clone of a base install before you do anything experimental, then create a clone from that for your experimentation.
That way, if/when you do something irrecoverable, you can simply delete and discard your work, make another clone from your Golden Image and try again very quickly.
Or,
Working in your openSUSE virtual machine, create a snapshot of your basic install and any major steps thereafter. If you do something irrecoverable, then simply roll back to one of your snapshots.

When you’re learning, expect that things will go wrong, but if you prepare well it will only mean a slight pause in your progress.

TSU

Hendrik,

got the following:

:~> systemctl status postgresql.service
**●** postgresql.service - LSB: Start the PostgreSQL master daemon
   Loaded: loaded (/etc/init.d/postgresql; generated; vendor preset: disabled)
   Active: **failed** (Result: exit-code) since Mon 2017-03-20 21:33:13 EET; 1 day 4h ago
     Docs: man:systemd-sysv-generator(8)
  Process: 6120 ExecStart=/etc/init.d/postgresql start **(code=exited, status=1/FAILURE)**

~> systemctl start postgresql.service   
Job for postgresql.service failed because the control process exited with error code.
See "systemctl status postgresql.service" and "journalctl -xe" for details.

and status reports were:

sudo systemctl status postgresql.service
[sudo] password for root:  
**●** postgresql.service - LSB: Start the PostgreSQL master daemon
   Loaded: loaded (/etc/init.d/postgresql; generated; vendor preset: disabled)
   Active: **failed** (Result: exit-code) since Wed 2017-03-22 01:44:35 EET; 9min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 31100 ExecStart=/etc/init.d/postgresql start **(code=exited, status=1/FAILURE)**

Mar 22 01:44:34 LAPTOP-5HS2NJ5L.home su[31112]: pam_unix(su-l:session): session opened f
Mar 22 01:44:34 LAPTOP-5HS2NJ5L.home su[31147]: **(to postgres) root on none**
Mar 22 01:44:34 LAPTOP-5HS2NJ5L.home su[31147]: pam_unix(su-l:session): session opened f
Mar 22 01:44:35 LAPTOP-5HS2NJ5L.home postgresql[31100]: Starting PostgreSQL 9.6.2 pg_ctl
Mar 22 01:44:35 LAPTOP-5HS2NJ5L.home postgresql[31100]: Examine the log output.
Mar 22 01:44:35 LAPTOP-5HS2NJ5L.home postgresql[31100]: ..failed
Mar 22 01:44:35 LAPTOP-5HS2NJ5L.home systemd[1]: **postgresql.service: Control process exi**
Mar 22 01:44:35 LAPTOP-5HS2NJ5L.home systemd[1]: **Failed to start LSB: Start the PostgreS**
Mar 22 01:44:35 LAPTOP-5HS2NJ5L.home systemd[1]: **postgresql.service: Unit entered failed**
Mar 22 01:44:35 LAPTOP-5HS2NJ5L.home systemd[1]: **postgresql.service: Failed with result**

sudo journalctl -xe                      
-- Subject: Unit UNIT has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--  
-- Unit UNIT has begun starting up.
Mar 22 02:07:46 LAPTOP-5HS2NJ5L.home systemd[31563]: Stopped target Paths.
-- Subject: Unit UNIT has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--  
-- Unit UNIT has finished shutting down.
Mar 22 02:07:46 LAPTOP-5HS2NJ5L.home systemd[31563]: Received SIGRTMIN+24 from PID 31600 (kill).
Mar 22 02:07:46 LAPTOP-5HS2NJ5L.home systemd[31564]: pam_unix(systemd-user:session): session closed for user postgres
Mar 22 02:07:46 LAPTOP-5HS2NJ5L.home systemd[1]: Stopped User Manager for UID 26.
-- Subject: Unit user@26.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--  
-- Unit user@26.service has finished shutting down.
Mar 22 02:07:46 LAPTOP-5HS2NJ5L.home systemd[1]: Removed slice User Slice of postgres.
-- Subject: Unit user-26.slice has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--  
-- Unit user-26.slice has finished shutting down.
Mar 22 02:07:46 LAPTOP-5HS2NJ5L.home kernel: **SFW2-INext-DROP-DEFLT IN=wlo1 OUT= MAC=30:e3:7a:88:7e:8a:f4:f5:d8:00:cf:44:08:00 SRC=192.168.10.37 DST=192.168.10.43 LEN=544 TOS=**
Mar 22 02:07:47 LAPTOP-5HS2NJ5L.home kernel: **SFW2-INext-DROP-DEFLT IN=wlo1 OUT= MAC=30:e3:7a:88:7e:8a:f4:f5:d8:00:cf:44:08:00 SRC=192.168.10.37 DST=192.168.10.43 LEN=544 TOS=**
Mar 22 02:07:48 LAPTOP-5HS2NJ5L.home kernel: **SFW2-INext-DROP-DEFLT IN=wlo1 OUT= MAC=30:e3:7a:88:7e:8a:f4:f5:d8:00:cf:44:08:00 SRC=192.168.10.37 DST=192.168.10.43 LEN=544 TOS=**
Mar 22 02:07:49 LAPTOP-5HS2NJ5L.home kernel: **SFW2-INext-DROP-DEFLT IN=wlo1 OUT= MAC=30:e3:7a:88:7e:8a:f4:f5:d8:00:cf:44:08:00 SRC=192.168.10.37 DST=192.168.10.43 LEN=544 TOS=**
Mar 22 02:08:34 LAPTOP-5HS2NJ5L.home sudo[31606]: **    simo : TTY=pts/1 ; PWD=/home/simo ; USER=root ; COMMAND=/usr/bin/systemctl status postgresql.service**
Mar 22 02:08:34 LAPTOP-5HS2NJ5L.home sudo[31606]: pam_unix(sudo:session): session opened for user root by simo(uid=0)
Mar 22 02:08:34 LAPTOP-5HS2NJ5L.home sudo[31606]: pam_systemd(sudo:session): Cannot create session: Already running in a session
Mar 22 02:08:34 LAPTOP-5HS2NJ5L.home sudo[31606]: pam_unix(sudo:session): session closed for user root
Mar 22 02:09:24 LAPTOP-5HS2NJ5L.home sudo[31613]: **    simo : TTY=pts/1 ; PWD=/home/simo ; USER=root ; COMMAND=/usr/bin/journalctl -xe**
Mar 22 02:09:24 LAPTOP-5HS2NJ5L.home sudo[31613]: pam_unix(sudo:session): session opened for user root by simo(uid=0)
Mar 22 02:09:24 LAPTOP-5HS2NJ5L.home sudo[31613]: pam_systemd(sudo:session): Cannot create session: Already running in a session

I did not make any changes in the file you mentioned.

Your last posted log suggests that your postgresql service was started successfully, but then intentionally shutdown by an identified thread.

I suspect you did something more than what you described, but what… it might be impossible to troubleshoot. You might try tracing the Pid described, but that thread might have also terminated as soon as it shut down the service.

So,
As I suggested before… You could try running some kind of trace utility (valgrind?) but at some point you should weigh the prospects of success and the effort you’re putting into troubleshooting vs just starting over.

TSU

One more thing to check. Please do a:

ps -ef | grep postgres

and post the results. An already running instance could prevent the start of another one.

If this does not shed any more light on the situation, I can only support tsu2’s recommendation to start over.

Hendrik

Tsu,

I do rember it: my thick/clumpsy fingers did some b-y trick and the terminal-window I was working in got closed before I exited the psql-terminal, Most likely all my problems started there.
Tsu, I’hv been looking for a virtualenv from Yast. (Used to use it during my Ubuntu-period,) Found python-virualenv and python-pip. Are those the same?
I would also have a look at BaseX with Racket. The reason is that Racket should have a rather extensive library for complicated networks simulation., and BaseX looks quite interesting. (SBCL and Clisp with PostgreSQL are other options)- BaseX is not available through Yast, but I assume that it would be pretty safe to install it in virtual environment.
I appreciate your help!

br Simo

Thanks Hendrik,

You are right, I mistakely closed the terminal I was working in. However, here is the response:

 ps -ef | grep postgres
simo     16596 16566  0 00:26 pts/3    00:00:00 grep --color=auto **postgres**

I agree, it’s better to star from the beginning and as TSU said, I’ll build it into a virtualenv,

Simo

Virtualenv is used to install multiple versions of Python and assign your choice to specific Python applications.

Has nothing to do with PostgeSQL.
You should be looking at installing maybe Virtualbox, that enables you to create and run an entire virtual machine complete with virtualized hardware and an entire OS.

TSU

Tsu, sorry to trouble you again
I insalled virtualbox in Yast (virtualbox, virtualbox-devel, libvirt-daemon-vbox, ) an made the necessary ‘vboxusers’ definitions in Yast. After that I opend the VirtualBox konsole (VirtualBox Graphical User Interface Version 5.1.18_SUSE r114002) and created an openSUSE VM on my hard drive. When trying to start the VM I get the error message

Kernel driver not installed (rc=-1908)
The VirtualBox Linux kernel driver (vboxdrv) is either not loaded or there is a permission problem with /dev/vboxdrv. Please reinstall the kernel module by executing
'/sbin/vboxconfig'
as root.

where: suplibOsInit what: 3 VERR_VM_DRIVER_NOT_INSTALLED (-1908) - The support driver is not installed. On linux, open returned ENOENT. 

But the /sbin/vboxconfig file does not exists, After searching the web for a solution

https://en.opensuse.org/SDB:VirtualBox_Installation

I installed kernel-devel in Yast. However, when trying to execute (after relogin) the setup command I get

**#** /etc/init.d/vboxdrv setup       
vboxdrv.sh: Stopping VirtualBox services.                                                                                             
vboxdrv.sh: Starting VirtualBox services.                                                                                             
vboxdrv.sh: failed: modprobe vboxdrv failed. Please use 'dmesg' to find out why.

Of the really long dmesg file I do not understand anything.

What the heck have I done wrong?

Simo

PS. I tried to remove PostgreSQL installation from my hard drive and clean everything, reinstall it, but did not get it working.

The first step always for troubleshooting a systemd service/app is to invoke its “status” like the following, which will return plenty of useful information including a hopefully relevant snippet from the logfile

systemctl status vbox.service

You should also list all the available packages to your system related to virtualbox, and consider which are or are not installed

zypper se virtualbox

You may find other useful postings others have made in the Virtualization forum (which generally is more appropriate for Virtualbox specific info)

TSU

Thanks,

I’ll go and check the other forums.
Here you have response to your questions:

systemctl status vbox.service                      
Unit vbox.service could not be found.


sudo zypper se virtualbox
Loading repository data...
Reading installed packages...

S | Name                           | Summary                                                     | Type        
--+--------------------------------+-------------------------------------------------------------+------------
  | RemoteBox: VirtualBox Client   | A remote client for administering VirtualBox and its guests | application
  | python-virtualbox              | Python bindings for virtualbox                              | package     
i | virtualbox                     | VirtualBox is an Emulator                                   | package     
  | virtualbox-devel               | Devel files for virtualbox                                  | package     
  | virtualbox-guest-desktop-icons | Icons for guest desktop files                               | package     
  | virtualbox-guest-kmp-default   | Guest kernel modules for VirtualBox                         | package     
  | virtualbox-guest-kmp-pae       | Guest kernel modules for VirtualBox                         | package     
  | virtualbox-guest-tools         | VirtualBox guest tools                                      | package     
  | virtualbox-guest-x11           | VirtualBox X11 drivers for mouse and video                  | package     
i | virtualbox-host-kmp-default    | Host kernel module for VirtualBox                           | package     
  | virtualbox-host-kmp-pae        | Host kernel module for VirtualBox                           | package     
  | virtualbox-host-source         | Source files for virtualbox kernel modules                  | package     
i | virtualbox-qt                  | Qt GUI part for virtualbox                                  | package     
  | virtualbox-websrv              | WebService GUI part for virtualbox                          | package  

as well as again

sudo /etc/init.d/vboxdrv setup
vboxdrv.sh: Stopping VirtualBox services.
vboxdrv.sh: Starting VirtualBox services.
vboxdrv.sh: failed: modprobe vboxdrv failed. Please use 'dmesg' to find out why.


br simo

Sorry, the virtualbox app/service should be vboxdrv, no vbox

systemctl status vboxdrv.service

TSU