|
|
| (2 intermediate revisions by 2 users not shown) |
| Line 1: |
Line 1: |
| = General =
| | #REDIRECT [[Postgresql]] |
| 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
| |
| | |
| = Install =
| |
| {{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.
| |
| | |
| = Configure =
| |
| 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;}}
| |
| | |
| = Use =
| |
| 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.
| |
| | |
| == modify the postgresql.conf file ==
| |
| {{Cmd|nano /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 pg_hba.conf file ==
| |
| {{Cmd|nano /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 start}}
| |
| | |
| Besure to allow the port through the firewall. For UFW 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.
| |
| | |
| = Troubleshooting =
| |
| * Some troubleshooting information
| |
| | |
| = Upgrading PostgreSQL =
| |
| * Notes on upgrading PostgreSQL 16 to 17
| |
| | |
| = Resources =
| |
| * https://blog.devart.com/configure-postgresql-to-allow-remote-connection.html
| |
| | |
| | |
| | |
| [[Category:Server]] | |