How to initialize postgresql with Ubuntu?

Question

I have the following problem, every time I turn on my machine, I go to the terminal and give sudo service postgresql status, always appears 9.3/main (port 5432): down.

On other machines I have installed, it starts together with Ubuntu, and when I run the same command appears 9.3/main (port 5432): online, I didn’t do anything different, but it seems that in my machine is missing some configuration.

Every time I use the database for the first time, you need to start the service.

At the time of installation, the following message was displayed, I do not know if it has anything to do with the error: No PostgreSQL clusters exist; see "man pg_createcluster"

I’m using Ubuntu 14.04 and Postgresql 9.3

Filing cabinet postgresql.conf:

# - Connection Settings -

#listen_addresses = 'localhost'     # what IP address(es) to listen on;
                # comma-separated list of addresses;
                # defaults to 'localhost'; use '*' for all
                # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                # (change requires restart)
#unix_socket_group = ''         # (change requires restart)
#unix_socket_permissions = 0777     # begin with 0 to use octal notation
                # (change requires restart)
#bonjour = off              # advertise server via Bonjour
                # (change requires restart)
#bonjour_name = ''          # defaults to the computer name
                # (change requires restart)

When installing Postgres the following information appears:

Removing obsolete dictionary files:
 * No PostgreSQL clusters exist; see "man pg_createcluster"
Processing triggers for ureadahead (0.100.0-16) ...
Configurando postgresql-9.3 (9.3.4-1) ...
Creating new cluster 9.3/main ...
 config /etc/postgresql/9.3/main
 data   /var/lib/postgresql/9.3/main
 locale pt_BR.UTF-8
 port   5432
update-alternatives: a usar /usr/share/postgresql/9.3/man/man1/postmaster.1.gz para disponibilizar /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) em modo automático
 * Starting PostgreSQL 9.3 database server  

Thank you.

Answer 1

If at any time of your installation you saw a message similar to:

warning: Please check that your locale settings:
 LANGUAGE = (unset),
 LC_ALL = (unset),
 LC_TIME = "de_DE.UTF-8",
 LC_MONETARY = "de_DE.UTF-8",
 LC_ADDRESS = "de_DE.UTF-8",
 LC_TELEPHONE = "de_DE.UTF-8",
 LC_NAME = "de_DE.UTF-8",
 LC_MEASUREMENT = "de_DE.UTF-8",
 LC_IDENTIFICATION = "de_DE.UTF-8",
 LC_NUMERIC = "de_DE.UTF-8",
 LC_PAPER = "de_DE.UTF-8",
 LANG = "en_US.UTF-8"
    are supported and installed on your system.

This means that Postgresql was unable to create the cluster because of the above error. To fix the issue:

1) Reconfigure your locales:

# dpkg-reconfigure locales

2) Create the cluster correctly (my version is 9.3):

# pg_createcluster 9.3 main --start

3) Start Postgresql:

# /etc/init.d/postgresql start

Answer 2

You probably didn’t put the postgresql to start in the boot. On the terminal run the command:

sudo update-rc.d postgresql defaults

For more information see update-rc.d(8).

Answer 3

Following the accepted answer and some information I saw in stackexchange: https://dba.stackexchange.com/questions/50906/why-wont-postgresql-9-3-start-on-ubuntu (if you still don’t solve try reading this)

Make sure the socket can be created

We have to make the socket can be created, so make sure that the directory /var/run/postgresql exist and have the correct permissions (to read and write)

Let’s do a test with TCP/IP

change your postgresql.conf to run on IP 127.0.0.1 to see what happens, there may be a bug with sockets maybe or something like that, although it is a kick you can try.

Properly starting

According to the original topic (bug?) To start postgre you will need the -h key, which specifies a host. This way:

psql -h 127.0.0.1

If it still doesn’t work

If nothing works, you have 2 options, in order of recommendation, 1 better and 2 worse:

1-apt-get with some hacks..

Since Ubuntu has its own package system, you can totally reinstall postgre by apt-get and some more hacks I found in Russian(?) http://it-admin.org/os-linux/warn-no-postgresql-clusters-exist-see-man-pg_createcluster-warning.html

sudo apt-get purge postgresql postgresql-contrib   # Remover configurações e o pacote

rm -r /etc/postgresql/
rm -r /etc/postgresql-common/
rm -r /var/lib/postgresql/
userdel -r postgres
groupdel postgres

sudo apt-get install postgresql postgresql-contrib # Reinstalar tudo

Now that we have installed, if we still give an error message with clusters:

pg_lsclusters
pg_createcluster 9.3 main --start

if not yet started, check your script at /etc/init. d/(postgresql?) and add that:

#-- EDITADO --#
#POSTGRE FIX#
# create socket directory
if [ -d /var/run/postgresql ]; then
  chmod 2775 /var/run/postgresql
else
  install -d -m 2775 -o postgres -g postgres /var/run/postgresql
  [ -x /sbin/restorecon ] && restorecon -R /var/run/postgresql || true
fi
#--/EDITADO--#

2- Format or give up your life

This is the least advised, but if it worked on the other machines, you could format it to see what happens... Or do a lot of gambiarra with the risk of screwing up the system.

Notes

You might want to take a look at this documentation file to help you set up: http://www.postgresql.org/docs/9.1/static/runtime-config-connection.html

And this issue that is also from the OS can help: https://dba.stackexchange.com/questions/50906/why-wont-postgresql-9-3-start-on-ubuntu

was all I found, if nothing works it is better you reinstall Ubuntu or create another partition for another installation, or still run on the VM...

Anything comments on the answer