PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

MantaRei · 2017-05-29 07:05:57

Hello all,

I have just successfully completed the steps below to install the latest version of Postgres and pgAdmin4
My previous questions on MySQL caused my thoughts to fork after meditating on long term needs. Postgres, I feel, is to be the better fit.
If there was an easier way to do this or if there are any mistakes, feel free to point out.
Also, please know this is just the install and I have not tried to actually use the database yet. There is still much to learn but getting this far in last 48hrs has been educational..

################################

#Install Postgres and client
# 1.) sudo to root ( i.e. user@yourbox:~# )

sudo apt-get install postgresql-9.6 postgresql-client

################################

PGAdmin4 ver 1.5 - Install & run instructions 05292017

################################

# 2.) Install virtalenv and other required packages...

sudo apt-get install build-essential #this may already be installed
sudo apt-get libssl-dev python-dev libgmp3-dev
sudo apt-get install virtualenv python-pip libpq-dev

#cd /opt
#mkdir enviornmentpy

#cd enviornmentpy
virtualenv pgadmin4

#cd pgadmin4
source bin/activate #this will activate the virtual enviornmnet

#The next line should look like this...
# " (pgadmin4) user@yourbox:/opt/environmentpy/pgadmin4# "

# 3.) Download and install PGAdmin4

wget https://ftp.postgresql.org/pub/pgadmin/ … ne-any.whl

pip install pgadmin4-1.5-py2.py3-none-any.whl

#cd lib/python2.7/site-packages/pgadmin4

#The next line should look like this...
# " (pgadmin4) user@yourbox:/opt/environmentpy/pgadmin4/lib/python2.7/site-packages/pgadmin4# "

touch config_local.py

#4.) Configuration ## We need to write the SERVER_MODE = False to run in single user mode...

echo "SERVER_MODE - False" >> lib/python2.7/site-packages/pgadmin4/config_local.py

#5.) Run PGAdmin4

python pgAdmin4.py

#Initial setup message will prompt you to enter email address and a password
#Follow prompts correctly and should see " database has been created at /root/.pgadmin/pgadmin4.db ..etc..etc "Please navigate to http://localhost:5050 in your browser"

#Open your browser and gain access by pasting the following into browser
http://localhost:5050

#Enter login/password
#In the General tab, name the DB
#In the Conneciton tab enter host address and port 5432 and database username/pass

...DONE

malinas · 2017-06-02 22:12:20

Please use BBCode to format your post, also, this might not be the correct section for that, but someone else should take care of it shortly.

MantaRei · 2017-06-04 02:18:11

test
test

Yes, after posting i thought should have stayed in "other issues" but too late..

golinux · 2017-06-04 02:42:24

Nice to see that you found the BBCode options. Could you please edit the commands in your original post? It would then look something like this.

MantaRei · 2017-06-06 04:01:14

#Install Postgres and client
#sudo to root ( i.e. user@yourbox:~# )

sudo apt-get install postgresql-9.6 postgresql-client

################################

#PGAdmin4 ver 1.5 - Install & run instructions 05292017
# Install virtalenv and other required packages...

sudo apt-get install build-essential     #this may already be installed
sudo apt-get libssl-dev python-dev libgmp3-dev
sudo apt-get install virtualenv python-pip libpq-dev 

#cd /opt
#mkdir environmentpy
 
#cd environnmentpy
virtualenv pgadmin4
 
#cd pgadmin4
source bin/activate     #this will activate the virtual environnmnet
 
#The next line should look like this...
      # " (pgadmin4) user@yourbox:/opt/environmentpy/pgadmin4# "

#Download and install PGAdmin4

wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.5/pip/pgadmin4-1.5-py2.py3-none-any.whl

pip install pgadmin4-1.5-py2.py3-none-any.whl

#cd lib/python2.7/site-packages/pgadmin4

#The next line should look like this...
      # " (pgadmin4) user@yourbox:/opt/environmentpy/pgadmin4/lib/python2.7/site-packages/pgadmin4# "

touch config_local.py

#Configuration ## We need to write the SERVER_MODE = False to run in single user mode...

echo "SERVER_MODE - False" >> lib/python2.7/site-packages/pgadmin4/config_local.py

#Run PGAdmin4
python pgAdmin4.py

#Initial setup message will prompt you to enter email address and a password
#Follow prompts correctly and should see " database has been created at /root/.pgadmin/pgadmin4.db  ..etc..etc "Please navigate to http://localhost:5050 in your   browser"

#Open your browser and gain access by pasting the following into browser
http://localhost:5050

#Enter login/password
#In the General tab, name the DB 
#In the Connection tab enter host address and port 5432 and database username/pass

fungus · 2017-07-16 16:07:04

Here is a quote of a postgresql related headache with a flood of logs
It will be interesting to see what your logs may look like after some use.
In about 10 days of discussion in another distro there hasn't been a reasonable solution to the problem, other than the original dilemma to switch systemd off.

Hi,
I'm running Debian server hosting Request Tracker with Postgresql as
database back-end. There is also gnupg-agent & dirmngr installed
(because of SMIME/GPG mail support). Systemd logs about starting user
sessions and Postgresql for some reason starts user sessions internally
frequently. This is the result of upgrade from Jessie to Stretch on
syslog size:
rt2:/etc/systemd# ll /var/log/syslog-2017* |tail
-rw-r----- 1 root adm 8058 Jun 28 06:25 /var/log/syslog-20170628.gz
-rw-r----- 1 root adm 7967 Jun 29 06:25 /var/log/syslog-20170629.gz
-rw-r----- 1 root adm 2537880 Jun 30 06:25 /var/log/syslog-20170630.gz
-rw-r----- 1 root adm 3111373 Jul 1 06:26 /var/log/syslog-20170701.gz
-rw-r----- 1 root adm 3265502 Jul 2 06:26 /var/log/syslog-20170702.gz
-rw-r----- 1 root adm 3264072 Jul 3 06:26 /var/log/syslog-20170703.gz
-rw-r----- 1 root adm 3241260 Jul 4 06:26 /var/log/syslog-20170704.gz
-rw-r----- 1 root adm 3255989 Jul 5 06:26 /var/log/syslog-20170705.gz
-rw-r----- 1 root adm 3256939 Jul 6 06:26 /var/log/syslog-20170706.gz
-rw-r----- 1 root adm 63687210 Jul 7 06:26 /var/log/syslog-20170707
I did upgrade on Jun 30...
The system log was full of
Jul 6 06:26:33 rt2 liblogging-stdlog: [origin software="rsyslogd" swVersion="8.24.0" x-pid="406" x-info
="http://www.rsyslog.com"] rsyslogd was HUPed
Jul 6 06:26:36 rt2 systemd[1]: Created slice User Slice of postgres.
Jul 6 06:26:36 rt2 systemd[1]: Starting User Manager for UID 109...
Jul 6 06:26:36 rt2 systemd[1]: Started Session c146966 of user postgres.
Jul 6 06:26:36 rt2 systemd[1860]: Listening on GnuPG cryptographic agent and passphrase cache (restricte
d).
Jul 6 06:26:36 rt2 systemd[1860]: Listening on GnuPG cryptographic agent (access for web browsers).
Jul 6 06:26:36 rt2 systemd[1860]: Listening on GnuPG cryptographic agent (ssh-agent emulation).
Jul 6 06:26:36 rt2 systemd[1860]: Listening on GnuPG network certificate management daemon.
Jul 6 06:26:36 rt2 systemd[1860]: Reached target Paths.
Jul 6 06:26:36 rt2 systemd[1860]: Reached target Timers.
Jul 6 06:26:36 rt2 systemd[1860]: Listening on GnuPG cryptographic agent and passphrase cache.
Jul 6 06:26:36 rt2 systemd[1860]: Reached target Sockets.
Jul 6 06:26:36 rt2 systemd[1860]: Reached target Basic System.
Jul 6 06:26:36 rt2 systemd[1860]: Reached target Default.
Jul 6 06:26:36 rt2 systemd[1860]: Startup finished in 11ms.
Jul 6 06:26:36 rt2 systemd[1]: Started User Manager for UID 109.
Jul 6 06:26:36 rt2 systemd[1]: Stopping User Manager for UID 109...
Jul 6 06:26:36 rt2 systemd[1860]: Stopped target Default.
Jul 6 06:26:36 rt2 systemd[1860]: Stopped target Basic System.
Jul 6 06:26:36 rt2 systemd[1860]: Stopped target Sockets.
Jul 6 06:26:36 rt2 systemd[1860]: Closed GnuPG network certificate management daemon.
Jul 6 06:26:36 rt2 systemd[1860]: Closed GnuPG cryptographic agent and passphrase cache.
Jul 6 06:26:36 rt2 systemd[1860]: Closed GnuPG cryptographic agent (access for web browsers).
Jul 6 06:26:36 rt2 systemd[1860]: Closed GnuPG cryptographic agent and passphrase cache (restricted).
Jul 6 06:26:36 rt2 systemd[1860]: Closed GnuPG cryptographic agent (ssh-agent emulation).
Jul 6 06:26:36 rt2 systemd[1860]: Reached target Shutdown.
Jul 6 06:26:36 rt2 systemd[1860]: Starting Exit the Session...
Jul 6 06:26:36 rt2 systemd[1860]: Stopped target Paths.
Jul 6 06:26:36 rt2 systemd[1860]: Stopped target Timers.
...
19 user sessions per minute :-/.
I found a bug report
https://bugs.debian.org/cgi-bin/bugrepo … bug=850982
But after disabling gnupg-agent & dirmngs sockets using:
systemctl --global mask --now dirmngr.socket
systemctl --global mask --now gpg-agent.service gpg-agent.socket gpg-agent-ssh.socket gpg-agent-extra.socket gpg-agent-browser.socket
I got this
Jul 7 12:16:36 rt2 systemd[21005]: gpg-agent-ssh.socket: Cannot add dependency job, ignoring: Unit gpg-agent-ssh.socket is masked.
Jul 7 12:16:36 rt2 systemd[21005]: gpg-agent.socket: Cannot add dependency job, ignoring: Unit gpg-agent.socket is masked.
Jul 7 12:16:36 rt2 systemd[21005]: gpg-agent-extra.socket: Cannot add dependency job, ignoring: Unit gpg-agent-extra.socket is masked.
Jul 7 12:16:36 rt2 systemd[21005]: gpg-agent-browser.socket: Cannot add dependency job, ignoring: Unit gpg-agent-browser.socket is masked.
Jul 7 12:16:36 rt2 systemd[21005]: dirmngr.socket: Cannot add dependency job, ignoring: Unit dirmngr.socket is masked.
instead of info about opening/closing sockets :-/
Finally I tried
loginctl enable-linger postgres
Now the only thing rest:
Jul 7 15:42:35 rt2 systemd[1]: Started Session c4504 of user postgres.
Jul 7 15:42:35 rt2 systemd[1]: Started Session c4505 of user postgres.
Jul 7 15:42:35 rt2 systemd[1]: Started Session c4506 of user postgres.
Jul 7 15:42:35 rt2 systemd[1]: Started Session c4507 of user postgres.
Jul 7 15:42:35 rt2 systemd[1]: Started Session c4508 of user postgres.
Jul 7 15:42:36 rt2 systemd[1]: Started Session c4509 of user postgres.
Every minute 19 lines of this.
I have Postgresql installed on several machines and every logs to two remote
syslog servers. Why store and transfer over net this cruft.
How I should get rid of this session management the right way? (Besides
switching to SysV init)
https://access.redhat.com/solutions/1564823
- this seems to me a bit a hack
Switch systemd LogLevel to notice?
(https://www.centos.org/forums/viewtopic.php?t=48785)
- maybe I will miss something in the future?
Isn't this a problem of Postgresql the user session opened so
frequently?
Regards
--
Zito

But here is a reasonable analysis of the problem

Václav Ovsík:
> How I should get rid of this session management the right way?
I have seen this systemd problem myself.
What is happening is that every time something SSHes in as user postgres,
systemd-logind is starting up a per-user instance of systemd along with with a
whole bunch of per-user socket units (and whatever else you have configured all
per-user service managers to start up); and whenever the SSH session finishes,
systemd-logind is dutifully shutting down that per-user instance.
There's no way to actually turn the per-user instance off, for accounts that
should *never* have per-user service managers. The best that you can do is
pretty much the opposite and turn it *always on*. You do this by telling
systemd-logind that the postgres user is a "lingering" user. There is a
loginctl command for doing so. Then it will start up the per-user instance of
systemd and leave it running.
(You could also remove the user@.service template outright, which removes
per-user service management for *all* user accounts, including those for real
human beings. However, this still results in log noise, as the failed attempts
by systemd-logind to start up user@NNNN services on every SSH login will all be
logged. In the "lingering" case, there is less log noise.)
Of course, the postgres account is most definitely an account which should never
have a per-user service manager. So, too, are dedicated accounts for things
like (say) Nagios monitoring.
But there's no mechanism for specifying such accounts, or (conversely, and more
usefully given the general ratio of general-purpose use to role accounts) for
specifying the accounts that should have a per-user service manager and saying
that all other accounts should not. So the best that you can do is be very
aware that everything installed and enabled in /usr/lib/systemd/user is going to
have an instance running with the user access of your postgres account, and be
very careful about what you put in there. (The gpg-agent package has already
dumped some GPG stuff there and enabled it, notice.)

This was the straw for me, to bail out while I was ahead of myself.
This complex simplification of an init system is like a bomb waiting to explode. Too many people trying so many different patches for a ship that is about to sink that it will become acceleratingly comlex to audit or manage. In a year or two nobody will be able to figure out why 99% is inside systemd code.

The problem with too many log files is not the size on the disk but the inablility to review anything and locate anything meaningful.

My 2cents, anyway.

Last edited by fungus (2017-07-16 16:15:18)

dan_mi_sun · 2017-11-17 11:23:40

@MantaRei - Basic question incoming...

Could you highlight how you were able to install `postgresql-9.6`

I get the following:

host% sudo apt-get install postgresql-9.6 postgresql-client
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package postgresql-9.6
E: Couldn't find any package by regex 'postgresql-9.6'

I can do the following:

host% sudo apt-cache policy postgresql                     
postgresql:
  Installed: (none)
  Candidate: 9.4+165+deb8u3
  Version table:
     9.4+165+deb8u3 0
        500 http://auto.mirror.devuan.org/merged/ jessie-security/main amd64 Packages
     9.4+165+deb8u2 0
        500 http://auto.mirror.devuan.org/merged/ jessie/main amd64 Packages

ralph.ronnquist · 2017-11-17 23:41:04

postgresql-9.6:
  Installed: (none)
  Candidate: 9.6.3-1~bpo8+1
  Version table:
     9.6.3-1~bpo8+1 0
        100 http://auto.mirror.devuan.org/merged/ jessie-backports/main amd64 Packages

The officially official Devuan Forum!

#1 2017-05-29 07:05:57

PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

#2 2017-06-02 22:12:20

Re: PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

#3 2017-06-04 02:18:11

Re: PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

#4 2017-06-04 02:42:24

Re: PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

#5 2017-06-06 04:01:14

Re: PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

#6 2017-07-16 16:07:04

Re: PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

#7 2017-11-17 11:23:40

Re: PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

#8 2017-11-17 23:41:04

Re: PostgreSQL 9.6 and pgAdmin4 v1.5 Installation

Board footer