.. _postgresql:

==========
PostgreSQL
==========

Install Postgres if you haven't already done it::

  sudo apt-get install postgresql postgresql-contrib


.. _postgresql-_10_12:

PostgreSQL 10.12
================

Once logged in to postgres using ``sudo su - postgres`` you can ``createuser -P USERNAME`` then ``createdb --owner USERNAME DATABASENAME`` and ``dropdb mydb``. To do anything with the actual database you can use the interactive terminal program using ``psql mydb`` which allows you to interactively enter, edit, and execute SQL commands.

`<https://www.postgresql.org/docs/10/tutorial-accessdb.html>`_

Consider routine maintenance `<https://www.postgresql.org/docs/10/maintenance.html>`_


Create and drop users
---------------------

`<https://www.postgresql.org/docs/10/app-createuser.html>`_
`<https://www.postgresql.org/docs/10/app-dropuser.html>`_

For web applications a user with no priveleges should be created for the database to be attached to. This increases security because log in information needs storing on the web server. There is an interactive way to create users but by default postgres creates users with no permissions::

  sudo su - postgres
  createuser -P USERNAME
  # enter password twice when prompted

If you make a mistake or are clearing out::

  sudo su - postgres
  dropuser USERNAME


Create and drop databases
-------------------------

`<https://www.postgresql.org/docs/10/app-createdb.html>`_
`<https://www.postgresql.org/docs/10/app-dropdb.html>`_

For web applications you should connect databases to a user with no priveleges::

  createdb --owner USERNAME DATABASENAME

Check what databases there are::

  psql
  \l  # To list all databases
  q  # To end the table view
  \q  # To quit out of psql
  
Drop databases::

  dropdb DATABASENAME


.. _postgresql_backup:

Backup
------

Backup and restore `<https://www.postgresql.org/docs/10/backup.html>`_

There's lots of different options but the simplest seems to be to use the ``pg_dump`` and ``pg_restore`` built in funtions.

To back up the PRODUCTION database::

  cd /
  sudo su - postgres
  pg_dump -Fc DATABASENAME > /tmp/2020-02-23_PROJECTdb.sql
  exit
  sudo cp /tmp/2020-02-23_PROJECTdb.sql /home/USER/PROJECT_proj/2020-02-23_PROJECTdb.sql
  #
  # Copy backup file to local dev machine
  #


.. _postgresql_restore:

Restore
-------

There are two stages - first you need to create the database and populate it with the required tables::

  sudo su - postgres
  createuser -P USERNAME
  createdb --owner USERNAME DATABASENAME
  exit
  # In your activated pip environment:
  python manage.py migrate

Once the database is created you can restore using Django :ref:`django_loaddata` or using ``postgres``::

  #
  # Only continue if not using Django loaddata
  #
  sudo su - postgres
  pg_restore --no-privileges --no-owner -c -d DATABASENAME /home/USER/DB.sql
  # REMOVE NO OWNER FLAG???
  # c means clean and d specifies the database
  psql
  ALTER ROLE dbusername SUPERUSER;
  # I'm not sure about the implicatons of this on the production machine.
  \q
  exit
  
`<https://stackoverflow.com/questions/37271402/pg-restore-error-role-xxx-does-not-exist>`_


Errors
------
  
`<https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge>`_
  
``/etc/postgresql/10/main/pg_hba.conf``::
  
  local   all             postgres                                peer

Should be::

  local   all             postgres                                md5
  
.. important:: Remember to ``sudo service postgresql restart`` after making changes.
  
.. note:: It was still an issue for me until I added 'localhost' as the host in the local_settings