PostgreSQL - Revision history

Porcelinux at 20:21, 16 February 2025

2025-02-16T20:21:33Z

← Older revision		Revision as of 20:21, 16 February 2025
Line 45:		Line 45:
	log_line_prefix = '%t %c %u ' # time sessionid user		log_line_prefix = '%t %c %u ' # time sessionid user
	log_statement = 'all'		log_statement = 'all'

			== Errors ==

			<pre>
			2025-02-16 16:05:08.491 UTC 67b20cb4.1fdcb 0 archiver archiver FATAL: password authentication failed for user "archiver"
			2025-02-16 16:05:08.491 UTC 67b20cb4.1fdcb 0 archiver archiver DETAIL: User "archiver" has no password assigned.
			Connection matched pg_hba.conf line 7: "local all all md5"
			2025-02-16 16:05:08.491 UTC 67b20cb4.1fdcb 0 archiver archiver LOG: could not send data to client: Broken pipe
			2025-02-16 16:05:08.510 UTC 67b20cb4.1fdd0 0 [unknown] archiver ERROR: replication slot "vpg_archiver" already exists
			2025-02-16 16:05:08.510 UTC 67b20cb4.1fdd0 0 [unknown] archiver STATEMENT: CREATE_REPLICATION_SLOT "vpg_archiver" PHYSICAL
			2025-02-16 16:05:08.817 UTC 67b20cb4.1fdd8 0 [unknown] archiver ERROR: requested WAL segment 0000000100000022000000C4 has already been removed
			2025-02-16 16:05:08.817 UTC 67b20cb4.1fdd8 0 [unknown] archiver STATEMENT: START_REPLICATION SLOT "vpg_archiver" 22/C4000000 TIMELINE 1
			<pre>

Xunil: /* Queries */

2025-01-24T07:50:46Z

Queries

← Older revision		Revision as of 07:50, 24 January 2025
Line 3:		Line 3:
	==Queries==		==Queries==
	*Create an Admin user from command line		*Create an Admin user from command line
	CREATE USER user WITH LOGIN PASSWORD '~~md530a210c89654b4a3a8255688086158a9~~' VALID UNTIL '2205-01-01' SUPERUSER ;		CREATE USER user WITH LOGIN PASSWORD 'md53Oa21Oc89654a4b3a8255688186I58a9' VALID UNTIL '2205-01-01' SUPERUSER ;

	* Get list of databases and their sizes:		* Get list of databases and their sizes:

Xunil: /* Shell */

2025-01-21T15:27:52Z

Shell

← Older revision		Revision as of 15:27, 21 January 2025
Line 20:		Line 20:
	host_src=10.1.1.1		host_src=10.1.1.1
	host_dst=10.1.1.2		host_dst=10.1.1.2
	PCOMMAND="psql -t -A -Upostgres ~~ipaylater_db~~"		PCOMMAND="psql -t -A -Upostgres db_name"

	tables=`$PCOMMAND -h$host_src -c "SELECT * FROM information_schema.tables WHERE table_type != 'VIEW'"\|awk -F"\|" '{print $2"."$3}'`		tables=`$PCOMMAND -h$host_src -c "SELECT * FROM information_schema.tables WHERE table_type != 'VIEW'"\|awk -F"\|" '{print $2"."$3}'`

Porcelinux at 20:01, 6 July 2015

2015-07-06T20:01:34Z

← Older revision		Revision as of 20:01, 6 July 2015
Line 1:		Line 1:
	~~''Potente e storico server SQL''~~
	=Command Line Tools=		=Command Line Tools=

Porcelinux at 20:01, 6 July 2015

2015-07-06T20:01:20Z

← Older revision		Revision as of 20:01, 6 July 2015
Line 1:		Line 1:
	''Potente e storico server SQL ~~by RedHat~~''		''Potente e storico server SQL''
			=Command Line Tools=

	* ~~Query al volo su un database Postgres~~		==Queries==
			*Create an Admin user from command line
			CREATE USER user WITH LOGIN PASSWORD 'md530a210c89654b4a3a8255688086158a9' VALID UNTIL '2205-01-01' SUPERUSER ;

			* Get list of databases and their sizes:
			SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname))
			FROM pg_database ORDER BY pg_database_size DESC;

			== Shell ==
			* Connect to a PostgreSQL server.
	export PGPASSWORD="password"		export PGPASSWORD="password"
	psql -t -A -h <hostname> <database> <utente> -c "SELECT myquery FROM table";		psql -t -A -h <hostname> <database> <utente> -c "SELECT myquery FROM table";

	* Get list of databases and their sizes:
	~~SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;~~

	* script to compare the row count on all tables between a master and a replica database (run in the replica with "trusted" user)		* script to compare the row count on all tables between a master and a replica database (run in the replica with "trusted" user)
Line 28:		Line 35:
	done		done
	</source>		</source>

			== Configuration ==
			*; some standard locations
			**home:/var/lib/postgres
			**conf:/etc/postgres/x.x/main/
			**logs:/var/log/postgres/x.x/main

	* More Logging		* More Logging
Line 36:		Line 49:

	= PostgreSQL GUI's =		= PostgreSQL GUI's =

	== phpPgAdmin ==		== phpPgAdmin ==

Porcelinux: /* phpPgAdmin */

2015-07-06T19:54:25Z

phpPgAdmin

← Older revision		Revision as of 19:54, 6 July 2015
Line 35:		Line 35:


			= PostgreSQL GUI's =
	== phpPgAdmin ==		== phpPgAdmin ==

	*; fix problem with ~~version~~ Postgres version 9.4		*; fix problem with Postgres version 9.4
	* taken and modified from: http://stackoverflow.com/questions/13467949/error-on-creating-database-in-phppgadmin		* taken and modified from: http://stackoverflow.com/questions/13467949/error-on-creating-database-in-phppgadmin
	Quick Fix: (worked with my Version (5.0.3) / pg 9.2.3 )		Quick Fix: (worked with my Version (5.0.3) / pg 9.2.3 )
Line 49:		Line 50:
	## replace ", spclocation," with ", pg_tablespace_location(oid) as spclocation," in both functions.		## replace ", spclocation," with ", pg_tablespace_location(oid) as spclocation," in both functions.
	## change class name to Postgres94		## change class name to Postgres94

			==PostgreSQLStudio ==
			http://www.postgresqlstudio.org/download/

			==TeamPostgreSQL==

			http://www.teampostgresql.com/

Porcelinux at 19:52, 6 July 2015

2015-07-06T19:52:44Z

@@ Line 3: / Line 3: @@
 * Query al volo su un database Postgres
   export PGPASSWORD="password"
-  psql -t -A -h <hostname> <database> <utente> -c "SELECT nome FROM ladri WHERE categoria LIKE '%mafioso%'";
+  psql -t -A -h <hostname> <database> <utente> -c "SELECT myquery FROM table";
 * Get list of databases and their sizes:
@@ Line 34: / Line 33: @@
   log_line_prefix = '%t %c %u ' # time sessionid user
   log_statement = 'all'

Xunil at 16:29, 25 February 2014

2014-02-25T16:29:18Z

← Older revision		Revision as of 16:29, 25 February 2014
Line 29:		Line 29:
	done		done
	</source>		</source>

			* More Logging
			add in postgresql.conf
			log_line_prefix = '%t %c %u ' # time sessionid user
			log_statement = 'all'

Xunil at 13:41, 11 December 2013

2013-12-11T13:41:00Z

← Older revision		Revision as of 13:41, 11 December 2013
Line 9:		Line 9:
	SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;		SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;

			* script to compare the row count on all tables between a master and a replica database (run in the replica with "trusted" user)
	<source lang=bash>		<source lang=bash>
	tables=`$PCOMMAND -c "select tablename from pg_tables;"`		tables=`$PCOMMAND -c "select tablename from pg_tables;"`

Xunil at 13:39, 11 December 2013

2013-12-11T13:39:55Z

← Older revision		Revision as of 13:39, 11 December 2013
Line 8:		Line 8:
	* Get list of databases and their sizes:		* Get list of databases and their sizes:
	SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;		SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;


			<source lang=bash>
			tables=`$PCOMMAND -c "select tablename from pg_tables;"`

			host_src=10.1.1.1
			host_dst=10.1.1.2
			PCOMMAND="psql -t -A -Upostgres ipaylater_db"

			tables=`$PCOMMAND -h$host_src -c "SELECT * FROM information_schema.tables WHERE table_type != 'VIEW'"\|awk -F"\|" '{print $2"."$3}'`

			IFS=$'\n'
			for TABLE in $tables ;do
			unset IFS
			count_src=`$PCOMMAND -h$host_src -c "select count(*) from $TABLE;"`
			count_dst=`$PCOMMAND -h$host_dst -c "select count(*) from $TABLE;"`
			if [ $count_src -ne $count_dst ];then
			echo -e "different record count betwen $host_src ($count_src)\t and $host_dst ($count_dst)\t in table $TABLE"
			fi
			done
			</source>