5.7. PostgreSQL

Install Postgres if you haven’t already done it:

sudo apt-get install postgresql postgresql-contrib

5.7.1. 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

5.7.1.1. 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

5.7.1.2. 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

5.7.1.3. 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
#

5.7.1.4. 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 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

5.7.1.5. 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