server_configuration:postgres [wiki.roman-halliday.com]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== PostgreSQL ======
My main requirement was PostgreSQL for DaVinci Resolve (see below) which required an older version of PostgreSQL. But some general notes on [[https://www.postgresql.org/|PostgreSQL]] are here.

General links:
  * [[https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-20-04]]
  * [[https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04]]

===== DigitalOcean Latest Version =====

==== Installation ====

If you don't want the included version, you can use the official apt repository from PostgreSQL: https://www.postgresql.org/download/linux/ubuntu/

<code bash>
sudo su

# Get any updates
apt-get update && apt-get upgrade -y

# View packages
apt-cache search postgres | grep '^postgresql-*[0-9]* '

# Find PostgreSQL versions (old syntax)
apt-cache search postgres

# Find the metapackage for all (somewhat superfluous)
apt-cache search postgres | grep postgres | grep metapackage

# Install ALL
apt-get install postgresql-all

# Install just the DB
apt-get install postgresql
</code>

==== usage ====

<code bash>
sudo -i -u postgres
psql
</code>
 or


<code bash>
sudo -i -u postgres psql
</code>

==== Allow Remote Connections ====

Taken from:
  * https://bosnadev.com/2015/12/15/allow-remote-connections-postgresql-database-server/

Tell postgresql to listen on everything
<file postgresql.conf>
listen_addresses = '*'         # Change from 'localhost' to '*'
</file>

Change IP range filtering to all addresses everywhere (comment out the existing line and add a new one as below)
<file pg_hba.conf>
#host   all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
</file>

<code bash>
vi /etc/postgresql/12/main/postgresql.conf
vi /etc/postgresql/12/main/pg_hba.conf
/etc/init.d/postgresql restart
</code>

==== Change firewall ====
  * https://www.digitalocean.com/community/tutorials/ufw-essentials-common-firewall-rules-and-commands#service-postgresql

<code bash>
ufw allow 5432
</code>

To close it again
<code bash>
ufw delete allow 5432
</code>

====== Uninstall PostgreSQL ======

Thanks to: https://askubuntu.com/questions/32730/how-to-remove-postgres-from-my-installation

<code bash>
# Stop the daemon
/etc/init.d/postgresql stop

sudo apt-get --purge remove postgresql*

# Clear Folders etc...
sudo rm -rf /var/lib/postgresql/
sudo rm -rf /var/log/postgresql/
sudo rm -rf /etc/postgresql/

# Remove accounts
sudo deluser postgres
sudo delgroup postgres

# Remove unused packages
sudo apt autoremove
</code>

Remove open firewall port:
<code bash>
sudo ufw delete allow 5432
</code>

====== PostgreSQL for DaVinci Resolve ======
DaVinci Resolve Project Server is the easy and best way to do work sharing and collaboration with resolve. It's supported under windows and mac. The latest version of this can be downloaded from the [[https://www.blackmagicdesign.com/support/family/davinci-resolve-and-fusion|Blackmagic Design support center]] under Latest Downloads.

DaVinci Resolve Project Server 17 (Version 17.0), uses ''PostgreSQL 9.5''. To use a remote database (I've got my own virtual server), one can download/install this on a linux machine:


  * [[https://www.qnap.com/en-in/how-to/tutorial/article/how-can-i-collaborate-davinci-resolve-studio-16-between-macos-and-windows-in-qts-4-4-3]]
  * https://www.postgresql.org/download/linux/ubuntu/

Add PostgreSQL package repository (taken from: https://www.postgresql.org/download/linux/ubuntu/)
<code bash>
# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update
</code>

Install specific version:
<code bash>
sudo apt-get -y install postgresql-9.6
</code>

Open up for Remote access (see above PostgreSQL section on configuration for remote access):
<code>
vi /etc/postgresql/9.6/main/postgresql.conf
vi /etc/postgresql/9.6/main/pg_hba.conf
/etc/init.d/postgresql restart
</code>

Open firewall:
<code bash>
ufw allow 5432
</code>

Change password for resolve usage:
<code>
root@roman-halliday:/home/david# sudo -i -u postgres psql
psql (9.6.21)
Type "help" for help.

postgres=# \password
Enter new password:
Enter it again:
postgres=# \q
</code>