Postgres - Revision history

M: /* Could not open shared memory segment */

2025-10-20T19:14:01Z

Could not open shared memory segment

← Older revision		Revision as of 19:14, 20 October 2025
Line 287:		Line 287:

	Well fuck it, let's try a reboot - who knows how long it has been... I updated ubuntu and got a postgresql v17 bump... rebooted... and I can connect again.		Well fuck it, let's try a reboot - who knows how long it has been... I updated ubuntu and got a postgresql v17 bump... rebooted... and I can connect again.

			NOTE that postgresql v18 was released 2025/09... i can wait more for that...

	==== If the god damned thing won't run ====		==== If the god damned thing won't run ====

M: /* Could not open shared memory segment */

2025-10-20T19:12:21Z

Could not open shared memory segment

← Older revision		Revision as of 19:12, 20 October 2025
Line 281:		Line 281:
	==== Could not open shared memory segment ====		==== Could not open shared memory segment ====

	~~Seems that's just another shitty sloppy Postgresql red herring error message. Once again, find the actual problem with~~ the complicated startup command:		Warning: the complicated startup command fails with 'existing lock file', since the server is actually running:
	sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf		sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf

	~~Here it was for this round of pain:~~
	2025-10-20 14:55:52.113 EDT [532774] FATAL: lock file "postmaster.pid" already exists		2025-10-20 14:55:52.113 EDT [532774] FATAL: lock file "postmaster.pid" already exists
	2025-10-20 14:55:52.113 EDT [532774] HINT: Is another postmaster (PID 524087) running in data directory "/var/lib/postgresql/17/main"?		2025-10-20 14:55:52.113 EDT [532774] HINT: Is another postmaster (PID 524087) running in data directory "/var/lib/postgresql/17/main"?

	~~I was trying to make a db backup with dbeaver, it failed. Maybe it left a postgresql process running?~~		Well fuck it, let's try a reboot - who knows how long it has been... I updated ubuntu and got a postgresql v17 bump... rebooted... and I can connect again.

	~~Stopping and starting the service did not seem to fix it, but once I stopped the process, there were no other postgres processes, so let's kill it:~~
	~~pk postgres~~
	~~# nothing~~
	~~sudo rm /var/lib/postgresql/17/main/postmaster.pid # nope, not there~~

	Well fuck it, let's try a reboot - who knows how long it has been...

	==== If the god damned thing won't run ====		==== If the god damned thing won't run ====

M: /* Troubleshooting */

2025-10-20T19:07:51Z

Troubleshooting

← Older revision		Revision as of 19:07, 20 October 2025
Line 278:		Line 278:

	=== Troubleshooting ===		=== Troubleshooting ===

			==== Could not open shared memory segment ====

			Seems that's just another shitty sloppy Postgresql red herring error message. Once again, find the actual problem with the complicated startup command:
			sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf

			Here it was for this round of pain:
			2025-10-20 14:55:52.113 EDT [532774] FATAL: lock file "postmaster.pid" already exists
			2025-10-20 14:55:52.113 EDT [532774] HINT: Is another postmaster (PID 524087) running in data directory "/var/lib/postgresql/17/main"?

			I was trying to make a db backup with dbeaver, it failed. Maybe it left a postgresql process running?

			Stopping and starting the service did not seem to fix it, but once I stopped the process, there were no other postgres processes, so let's kill it:
			pk postgres
			# nothing
			sudo rm /var/lib/postgresql/17/main/postmaster.pid # nope, not there

			Well fuck it, let's try a reboot - who knows how long it has been...

	==== If the god damned thing won't run ====		==== If the god damned thing won't run ====

M at 20:54, 25 June 2025

2025-06-25T20:54:40Z

← Older revision		Revision as of 20:54, 25 June 2025
Line 1:		Line 1:
	=== Usage ===		=== Usage ===

	==== dbeaver-ce ====		==== Access data via dbeaver-ce ====

	Use dbeaver-ce to access any databases from anywhere.		Use dbeaver-ce to access any databases from anywhere.

	~~OLD: PGAdmin is running on bitpost and can connect to any db on the LAN.~~

	==== Make a copy of a table ====		==== Make a copy of a table ====
Line 105:		Line 103:
	/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf].template		/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf].template

	It is likely there is a config/ubuntu/machine/etc with actual config files. ~~Stow copies~~ them ~~into~~ place, ~~I hope, bc~~ symlinks ~~break postgres~~.		It is likely there is a config/ubuntu/machine/etc with actual config files, bc symlinks break postgres. Prolly had to do a shitton of bullshit to put them in place. Some day, improve stow for fuckign shit apps that won't allow symlinks.
	/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf]		/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf]
	Copied from:		Copied from:
Line 137:		Line 135:
	=== Config ===		=== Config ===
	==== Configure remote access ====		==== Configure remote access ====
			See the actual config files for latest, but here's general notes.

	postgresql.conf:		postgresql.conf:
	# MDM We must also LISTEN if we want to allow any remote connections.		# MDM We must also LISTEN if we want to allow any remote connections.
Line 144:		Line 144:
	pg_hba.conf:		pg_hba.conf:
	# MDM my lan connection		# MDM my lan connection
	host all all abtdev1~~.bitpost.lan md5~~		host all all abtdev1 scram-sha-256
	host all all cast~~.bitpost.lan md5~~		host all all cast scram-sha-256

	==== Add a user + db ====		==== Add a user + db ====
Line 384:		Line 384:
	Here is the location to put the config files when installed from source with [mh-install-postgres]:		Here is the location to put the config files when installed from source with [mh-install-postgres]:
	/home/m/development/postgres/data/[postgresql.conf,pg_hba.conf]		/home/m/development/postgres/data/[postgresql.conf,pg_hba.conf]

			OLD: PGAdmin is running on bitpost and can connect to any db on the LAN.

M: /* Usage */

2025-06-25T19:13:28Z

Usage

← Older revision		Revision as of 19:13, 25 June 2025
Line 1:		Line 1:
	=== Usage ===		=== Usage ===

	==== ~~PGAdmin~~ ====		==== dbeaver-ce ====

	PGAdmin is running on bitpost and can connect to any db on the LAN.		Use dbeaver-ce to access any databases from anywhere.

			OLD: PGAdmin is running on bitpost and can connect to any db on the LAN.

	==== Make a copy of a table ====		==== Make a copy of a table ====

M: /* Issues with 17 */

2025-06-25T19:12:00Z

Issues with 17

@@ Line 185: / Line 185: @@
        it is UGLY as fuck and uses hardcoded version (17 as of today)
        I HATE POSTGRESQL
 === Troubleshooting ===

M: /* Install */

2025-06-01T18:49:32Z

Install

← Older revision		Revision as of 18:49, 1 June 2025
Line 89:		Line 89:
	=== Install ===		=== Install ===

	~~==== Postgres apt repo ====~~		You will need to manage your versions. [[Postgres#Upgrade\|Upgrading]] is painful, be informed before you start.
	~~Add the apt repo like this (see [https://wiki.postgresql.org/wiki/Apt offical instructions]):~~
	~~sudo apt install -y postgresql-common~~
	~~sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh~~

	~~Now you~~ will need to manage your versions. [[Postgres#Upgrade\|Upgrading]] is painful, be informed before you start.

	==== My install script ====		==== My install script ====
Line 104:		Line 99:

	* The config files are here (created and stowed by the script). You should read and update them to adjust the performance settings to match the hardware.		* The config files are here (created and stowed by the script). You should read and update them to adjust the performance settings to match the hardware.
	~~/var/lib/postgresql/13/main/[postgresql.conf,pg_hba.conf]~~
	~~# symlinked to...~~
	~~/home/m/development/config/ubuntu/<hostname>/var/lib/postgresql/13/main/[postgresql.conf,pg_hba.conf]~~

	Always change the postgres user's default password:		These gets symlinked from config/common/etc:
			/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf].template

			It is likely there is a config/ubuntu/machine/etc with actual config files. Stow copies them into place, I hope, bc symlinks break postgres.
			/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf]
			Copied from:
			/home/m/development/config/ubuntu/<hostname>/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf]

			Always change the postgres user's default password (you might get prompted for this during install):
	sudo su postgres		sudo su postgres
	psql		psql

M: /* Upgrade */

2025-06-01T18:41:01Z

Upgrade

← Older revision		Revision as of 18:41, 1 June 2025
Line 166:		Line 166:

	See the first troubleshooting tip when it inevitably breaks and nothing works. FUCK YOU POSTGRESQL DEVS.		See the first troubleshooting tip when it inevitably breaks and nothing works. FUCK YOU POSTGRESQL DEVS.

			==== Issues with 17 ====
			I backed up db with dbeaver-ce, but still had a lot of issues upgrading. I tried to update tooling where needed.

			Two big issues with 17 upgrade were:
			* i needed to call pg_createcluster myself (WHYYYY)
			* i debugged a LOT of config issues by running postgres manually with:
			sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
			that was the ONLY WAY I COULD GET ERRORS out of the fucking thing
			log: nope, journald: nope... FUCK YOU
			but after I fixed all the conf issues, it got MUCH WORSE
			the manual run worked, but the FUCKING UBUNTU SERVICE did not, WHY?
			finally i realized that:
			the conf files could NOT BE BEHIND A SYMLINK for some stupid hacky reason related to the service
			see here:
			https://askubuntu.com/questions/830346/postgresql-server-doesnt-start
			so i fixed setup_stow.sh to undo the symlinks after placing them
			it is UGLY as fuck and uses hardcoded version (17 as of today)
			I HATE POSTGRESQL

	=== Troubleshooting ===		=== Troubleshooting ===

M: /* Troubleshooting */

2025-06-01T16:54:15Z

Troubleshooting

← Older revision		Revision as of 16:54, 1 June 2025
Line 169:		Line 169:
	=== Troubleshooting ===		=== Troubleshooting ===

	If the god damned thing won't run~~, it~~ won't give you ANY fucking feedback as it's buried deep in the mines of Mordor.		==== If the god damned thing won't run ====
			It won't give you ANY fucking feedback as it's buried deep in the mines of Mordor.

	USE THIS to see what is going wrong!		USE THIS to see what is going wrong!

	sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -c config_file=/etc/postgresql/17/main/postgresql.conf		sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -c config_file=/etc/postgresql/17/main/postgresql.conf

			==== Can't connect ====

			Check the log:

			cat /var/log/postgresql/postgresql-17-main.log

	==== Finding bad huge data ====		==== Finding bad huge data ====

M: /* Upgrade */

2025-06-01T16:52:36Z

Upgrade

← Older revision		Revision as of 16:52, 1 June 2025
Line 164:		Line 164:
	* setup_stow.sh puts template config files in place but it has to use the version number		* setup_stow.sh puts template config files in place but it has to use the version number
	* config/common/etc/postgres has template config files, and config/ubuntu/#machine#/etc/postgres has working config files		* config/common/etc/postgres has template config files, and config/ubuntu/#machine#/etc/postgres has working config files

			See the first troubleshooting tip when it inevitably breaks and nothing works. FUCK YOU POSTGRESQL DEVS.

	=== Troubleshooting ===		=== Troubleshooting ===