|
|
| Line 1: |
Line 1: |
| PostgreSQL is a well known opensource database that scales well and is easy to use. In Alpine v3.20 we can install the latest version using the package postgresql16
| | #REDIRECT [[Postgresql]] |
| | |
| == Installation ==
| |
| {{Cmd|apk add {{pkg|postgresql16|arch=}} {{pkg|postgresql16-contrib|arch=}} {{pkg|postgresql16-openrc|arch=}}}}
| |
| {{Cmd|rc-update add postgresql}}
| |
| {{Cmd|rc-service postgresql start}}
| |
| | |
| This will start the postgresql 16 server and perform some initial configuration.
| |
| | |
| == Configuration ==
| |
| | |
| Login as the postgres user and start psql to create a new user and database:
| |
| {{Cmd|su postgres}}
| |
| {{Cmd|psql}}
| |
| {{Cmd|create user user with encrypted password 'password';}}
| |
| {{Cmd|create database database;}}
| |
| {{Cmd|grant all privileges on database database to user;}}
| |
| | |
| === Network access ===
| |
| | |
| By default only local access is allowed to PostgreSQL. To allow other networked services to access the database we need to configure PostgreSQL to allow external connections.
| |
| | |
| Edit the {{Path|/etc/postgresql16/postgresql.conf}} file using <code>nano</code> or any other {{ic|<editor> /etc/postgresql16/postgresql.conf}}
| |
| Find the line that starts with <pre>#listen_addresses = 'localhost'</pre>
| |
| Uncomment it and change it to the following:
| |
| <pre>listen_addresses = '*'</pre>
| |
| If you want it to listen on a specific ip you can change * to 192.168.1.2/24.
| |
| Save the file and change the next config file.
| |
| | |
| Modify the {{Path|/etc/postgresql16/pg_hba.conf}} file using <code>nano</code> or any other {{ic|<editor> /etc/postgresql16/pg_hba.conf}}
| |
| Look for the line: <pre>host all all 127.0.0.1/32 md5</pre>
| |
| And change it to: <pre>host all all 0.0.0.0/0 md5</pre>
| |
| This line allows connections from any IP address and requires a password for authentication (md5).
| |
| Restart the server to allow incoming connections from other hosts. {{Cmd|rc-service postgresql restart}}
| |
| | |
| Allow the port through the firewall. For [[UFW]] firewall type: {{Cmd|ufw allow 5432}}
| |
| | |
| This is a basic configuration. You can configure the PostgreSQL server to only allow certain networks or IP's to connect but thats beyond the scope of this documentation.
| |
| | |
| == Upgrading PostgreSQL ==
| |
| | |
| {{Todo| Need to add Notes on upgrading PostgreSQL }}
| |
| | |
| == Troubleshooting ==
| |
| | |
| {{Todo|Need to add troubleshooting examples}}
| |
| | |
| == See also ==
| |
| * https://blog.devart.com/configure-postgresql-to-allow-remote-connection.html
| |
| | |
| [[Category:Database]]
| |
| | |
| [[Category:Server]]
| |