Postgresql – How to figure out how Postgres was installed on the Linux system

linuxpostgresqlstartup

I'm using Postges 9.6 on Ubuntu Linux 16. I'm trying to figure out how the Postgres server was installed so I can figure out a command to start and stop it. It evidently isn't installed as a Linux service

[rails@server ~]$ sudo service postgresql status
Redirecting to /bin/systemctl status postgresql.service
Unit postgresql.service could not be found.

"which psql" reveals

[rails@server ~]$ which psql
/usr/bin/psql

I'm sort of at a loss for the next steps in figure out the command to start and stop it.

Best Answer

If you are simply looking to start and stop it, and a service or systemctl module doesn't exist on your machine, you can use pg_ctl to start and stop the Postgres server. You will also need to have the path to the data directory handy (typically, the folder that contains postgresql.conf and pg_hba.conf), and pass that in along with the -D flag:

pg_ctl -D /path/to/data_dir start|stop

If no data directory exists, you will either need further assistance to help you get an existing database cluster up and running, or you can use initdb to create a new database cluster

Related Solutions

Postgresql – which postgres do i have installed? what directory needs to be in the path to run initdb

Based on your situation, it would make sense I think to disable whatever PostgreSQL you have and start over with a fresh install. We are dealing with completely different applications that have nothing to do with eachother. In reality, PostgreSQL is only needed to serve up data. It makes sense to me, to simplify your life by installing PostgreSQL seperately and treating it as a totally independant application.

The simplest way to go about this, would be to take the following steps.

Determine if PostgreSQL is currently running A. If it is, locate it and disable it B. If it isn't move onto step 2
Download PostgreSQL 9.2 from PostgreSQL's website
Install dependancies
Configure, compile and install postgresql
Initalize your database and start postgres
What's next

1. Determine if PostgreSQL is currently running

From the shell, type the following

[root@someserver ~]# netstat -tlpn | grep postmaster
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      1119/postmaster     
tcp        0      0 :::5432                     :::*                        LISTEN      1119/postmaster

Postmaster on my machine is the PostgreSQL service. It is listening on port 5432 and has a process id of 1119.

Where is it?

Now that we have the process ID we can locate the files being used. lsof is handy because it will show you all the files and sockets a given process is using.

[root@someserver ~]# lsof -p 1119
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF   NODE NAME
postmaste 1119 postgres  cwd    DIR              253,3     4096 131093 /var/lib/pgsql/9.1/data
postmaste 1119 postgres  rtd    DIR              253,0     4096      2 /
postmaste 1119 postgres  txt    REG              253,4  5205008  27145 /usr/pgsql-9.1/bin/postgres

On my server, it is using /usr/pgsql-9.1/bin/postgres to run the service and the /var/lib/pgsql/9.1/data is the data directory.

From this point you know where its running and what directories its using.

On your server, it looks like its installed in /usr/local/Cellar/postgresql. If you have to start it manually, then you can just leave it alone. When you install PostgreSQL from source, it will install in /usr/local/pgsql by default.

It is only important to be sure that the postgresql currently installed on your system is not using port 5432. This is because two services can not use the same port. If it is, you will need to kill the process and keep it from starting again. Based the contents of your question, it looks like that won't be a problem.

2. Download postgresql

From the shell

[root@someserver ~]# cd ~ 
[root@someserver ~]# wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz
[root@someserver ~]# tar -zxvf postgresql-9.2.2.tar.gz

3. Install dependencies

I didn't notice you mentionning what flavor of Linux you were running. You can identify your OS by running cat /etc/issue.

PostgreSQL depends on several libraries.

On CentOS, I would run this to install the libraries I needed

[root@someserver ~]# yum install readline-devel zlib-devel make gcc g++

On Debian, you would use apt-get to install and apt-cache search to locate. I included build-essential because you'll need it to compile postgres.

Something like apt-get install libreadline6 libreadline6-dev build-essential

Not sure about OSX though

4. Configure, Compile and Install PostgreSQL

[root@someserver ~]# 
cd postgresql-9.2.2
The --bindir option will put the the postgresql binaries like pg_ctl, initdb, createdb all in the /usr/bin directory. This will be handy for you since you mentionned having trouble finding them. 
./configure --bindir=/usr/bin
make
make install

You can check to see if the binaries are available

[root@someserver ~]# psql -V
psql (PostgreSQL) 9.2.2

Once this is done, normally you would need to add the postgres user, but he's already on your system You do however need to create a data directory and give postgres read/write access to it

[root@someserver ~]# mkdir -p /var/lib/pgsql/data
[root@someserver ~]# chown postgres:postgres -R /var/lib/pgsql/data
[root@someserver ~]# su - postgres

PostgreSQL is installed You have created a data directory Now you can initalize your database

5. Initalize and start postgres

initdb -D /var/lib/pgsql/data

This will the cluster and your configuration files such as postgresql.conf, pg_hba.conf which you will need to modify later based on the users that need access, their ip addresses and what ip address and port postgresql will listen on

Now you can start PostgreSQL with the following command :

/usr/local/pgsql/bin/postmaster -D /var/lib/pgsql/data > /var/lib/pgsql/logfile 2>&1 &

When you run netstat -tlpn again, you should see

[root@someserver ~]# netstat -tlpn | grep postmaster
tcp        0      0 127.0.0.1:5432                127.0.0.1:*                   LISTEN      8721/postmaster

It is only listening to local connections for now, because by defaut that's what postgresql.conf is configured to do.

6. What's next

From here, you would need to configure postgresql.conf and pg_hba.conf appropriately. pg_hba.conf defines who can connect, from where and to what. postgresql.conf is used to configure the entire service, what ip address or addresses it listens on, what port number, maintenance cyles, etc. For your purposes, you will probabaly only be interested in changing this variable

listen_addresses = '*'

You may also want to create an init script in the /etc/init.d directory, so it will start automatically. How you go about that also depends on what flavor of Linux you are running.

If you run into any problems let me know. Since you are probably not using the exact same system, you may run into problems I haven't discussed here.

PostgreSQL – How to Move Data Directory on Ubuntu

First make sure the file exists really in that location. Run ls $PGDATA if there is a mistake in the environment variable you will either see the wrong files or get an error because the path does not exist.

Then make sure the owner of the parent directory /home/destination_data_directory and all directories and files under match the user you start pg_ctl under. If they are not either use

su postgres_user

to switch to the right user before running pg_ctl or use

chown -R postgres_user /home/destination_data_directory

to change the owner of the directory and everything under it.

Best Answer

Related Solutions

Postgresql – which postgres do i have installed? what directory needs to be in the path to run initdb

PostgreSQL – How to Move Data Directory on Ubuntu

Related Question