Postgresql 16 - Revision history

Prabuanand: all content moved to Postgresql

2025-10-31T16:33:52Z

all content moved to Postgresql

← Older revision		Revision as of 16:33, 31 October 2025
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~~]]

Dvh312: Fix restart command

2025-02-18T02:01:01Z

Fix restart command

← Older revision		Revision as of 02:01, 18 February 2025
Line 32:		Line 32:
	And change it to: <pre>host all all 0.0.0.0/0 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).		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~~}}		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}}		Allow the port through the firewall. For [[UFW]] firewall type: {{Cmd\|ufw allow 5432}}

Prabuanand: fixed wikitags, heading levels and links and added todo template

2025-01-16T08:53:28Z

fixed wikitags, heading levels and links and added todo template

← Older revision		Revision as of 08:53, 16 January 2025
Line 1:		Line 1:
	~~= General =~~
	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		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~~ =		== Installation ==
	{{Cmd\|apk add {{pkg\|postgresql16\|arch=}} {{pkg\|postgresql16-contrib\|arch=}} {{pkg\|postgresql16-openrc\|arch=}}}}		{{Cmd\|apk add {{pkg\|postgresql16\|arch=}} {{pkg\|postgresql16-contrib\|arch=}} {{pkg\|postgresql16-openrc\|arch=}}}}
	{{Cmd\|rc-update add postgresql}}		{{Cmd\|rc-update add postgresql}}
Line 9:		Line 8:
	This will start the postgresql 16 server and perform some initial configuration.		This will start the postgresql 16 server and perform some initial configuration.

	= ~~Configure~~ =		== Configuration ==

	Login as the postgres user and start psql to create a new user and database:		Login as the postgres user and start psql to create a new user and database:
	{{Cmd\|su postgres}}		{{Cmd\|su postgres}}
Line 17:		Line 17:
	{{Cmd\|grant all privileges on database database to user;}}		{{Cmd\|grant all privileges on database database to user;}}

	= ~~Use~~ =		=== 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.		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 ==		Edit the {{Path\|/etc/postgresql16/postgresql.conf}} file using <code>nano</code> or any other {{ic\|<editor> /etc/postgresql16/postgresql.conf}}
	{{~~Cmd~~\|~~nano~~ /etc/postgresql16/postgresql.conf}}
	Find the line that starts with <pre>#listen_addresses = 'localhost'</pre>		Find the line that starts with <pre>#listen_addresses = 'localhost'</pre>
	Uncomment it and change it to the following:		Uncomment it and change it to the following:
Line 28:		Line 28:
	Save the file and change the next config file.		Save the file and change the next config file.

	~~== modify~~ the pg_hba.conf file ==		Modify the {{Path\|/etc/postgresql16/pg_hba.conf}} file using <code>nano</code> or any other {{ic\|<editor> /etc/postgresql16/pg_hba.conf}}
	{{~~Cmd~~\|~~nano~~ /etc/postgresql16/pg_hba.conf}}
	Look for the line: <pre>host all all 127.0.0.1/32 md5</pre>		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>		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).		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.		Restart the server to allow incoming connections from other hosts. {{Cmd\|rc-service postgresql start}}
	{{Cmd\|rc-service postgresql start}}

	~~Besure to allow~~ the port through the firewall. For UFW type:		Allow the port through the firewall. For [[UFW]] firewall type: {{Cmd\|ufw allow 5432}}
	{{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.		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~~ =		== Upgrading PostgreSQL ==
	* Some troubleshooting information
			{{Todo\| Need to add Notes on upgrading PostgreSQL }}

	= ~~Upgrading PostgreSQL~~ =		== Troubleshooting ==
	* Notes on upgrading PostgreSQL 16 to 17

	= ~~Resources~~ =		{{Todo\|Need to add troubleshooting examples}}

			== See also ==
	* https://blog.devart.com/configure-postgresql-to-allow-remote-connection.html		* https://blog.devart.com/configure-postgresql-to-allow-remote-connection.html

			[[Category:Database]]

	[[Category:Server]]		[[Category:Server]]

Zcrayfish: /* Install */ Use pkg template.

2024-07-26T17:19:41Z

Install: Use pkg template.

← Older revision		Revision as of 17:19, 26 July 2024
Line 3:		Line 3:

	= Install =		= Install =
	{{Cmd\|apk add postgresql16 postgresql16-contrib postgresql16-openrc}}		{{Cmd\|apk add {{pkg\|postgresql16\|arch=}} {{pkg\|postgresql16-contrib\|arch=}} {{pkg\|postgresql16-openrc\|arch=}}}}
	{{Cmd\|rc-update add postgresql}}		{{Cmd\|rc-update add postgresql}}
	{{Cmd\|rc-service postgresql start}}		{{Cmd\|rc-service postgresql start}}

	This will start the postgresql 16 server and perform some initial configuration.		This will start the postgresql 16 server and perform some initial configuration.

	= Configure =		= Configure =

Jhjacobs81: /* modify the pg_hba.conf file */

2024-06-30T12:28:57Z

modify the pg_hba.conf file

← Older revision		Revision as of 12:28, 30 June 2024
Line 30:		Line 30:
	== modify the pg_hba.conf file ==		== modify the pg_hba.conf file ==
	{{Cmd\|nano /etc/postgresql16/pg_hba.conf}}		{{Cmd\|nano /etc/postgresql16/pg_hba.conf}}
	~~look~~ for the line: host all all 127.0.0.1/32 md5		Look for the line: <pre>host all all 127.0.0.1/32 md5</pre>
	~~and~~ change it to: host all all 0.0.0.0/0 md5		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).		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.		Restart the server to allow incoming connections from other hosts.

Jhjacobs81: /* Configure */

2024-06-30T12:27:52Z

Configure

← Older revision		Revision as of 12:27, 30 June 2024
Line 10:		Line 10:

	= Configure =		= Configure =
	~~login~~ as the postgres user and start psql to create a new user and database:		Login as the postgres user and start psql to create a new user and database:
	{{Cmd\|su postgres}}		{{Cmd\|su postgres}}
	{{Cmd\|psql}}		{{Cmd\|psql}}

Jhjacobs81: /* General */

2024-06-30T12:27:39Z

General

← Older revision		Revision as of 12:27, 30 June 2024
Line 1:		Line 1:
	= General =		= General =
	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		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 =		= Install =

Jhjacobs81: /* Use */

2024-06-30T12:26:11Z

Use

← Older revision		Revision as of 12:26, 30 June 2024
Line 18:		Line 18:

	= Use =		= 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.		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 ==		== modify the postgresql.conf file ==
Line 39:		Line 39:
	{{Cmd\|ufw allow 5432}}		{{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.		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 =		= Troubleshooting =

Jhjacobs81: /* Configure */

2024-06-30T12:25:32Z

Configure

← Older revision		Revision as of 12:25, 30 June 2024
Line 13:		Line 13:
	{{Cmd\|su postgres}}		{{Cmd\|su postgres}}
	{{Cmd\|psql}}		{{Cmd\|psql}}
	{{Cmd\|create user ~~myuser~~ with encrypted password '~~mypass~~';}}		{{Cmd\|create user user with encrypted password 'password';}}
	{{Cmd\|create database ~~mydb~~;}}		{{Cmd\|create database database;}}
	{{Cmd\|grant all privileges on database ~~mydb~~ to ~~myuser~~;}}		{{Cmd\|grant all privileges on database database to user;}}

	= Use =		= Use =

Jhjacobs81 at 12:24, 30 June 2024

2024-06-30T12:24:33Z

← Older revision		Revision as of 12:24, 30 June 2024
Line 33:		Line 33:
	and change it to: host all all 0.0.0.0/0 md5		and change it to: host all all 0.0.0.0/0 md5
	This line allows connections from any IP address and requires a password for authentication (md5).		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 =		= Troubleshooting =

← Older revision		Revision as of 12:25, 30 June 2024
Line 13:		Line 13:
	{{Cmd\|su postgres}}		{{Cmd\|su postgres}}
	{{Cmd\|psql}}		{{Cmd\|psql}}
	{{Cmd\|create user ~~myuser~~ with encrypted password '~~mypass~~';}}		{{Cmd\|create user user with encrypted password 'password';}}
	{{Cmd\|create database ~~mydb~~;}}		{{Cmd\|create database database;}}
	{{Cmd\|grant all privileges on database ~~mydb~~ to ~~myuser~~;}}		{{Cmd\|grant all privileges on database database to user;}}

	= Use =		= Use =