Difference between revisions of "PostgreSQL"
From Alessandro's Wiki
Porcelinux (talk | contribs) |
|||
(6 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
=Command Line Tools= | |||
* | ==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 | psql -t -A -h <hostname> <database> <utente> -c "SELECT myquery FROM table"; | ||
* | * 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> | |||
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> | |||
== Configuration == | |||
*; some standard locations | |||
**home:/var/lib/postgres | |||
**conf:/etc/postgres/x.x/main/ | |||
**logs:/var/log/postgres/x.x/main | |||
* More Logging | |||
add in postgresql.conf | |||
log_line_prefix = '%t %c %u ' # time sessionid user | |||
log_statement = 'all' | |||
= PostgreSQL GUI's = | |||
== phpPgAdmin == | |||
*; fix problem with Postgres version 9.4 | |||
* 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 ) | |||
#cd classes/database | |||
#copy Postgres84.php to Postgres94.php | |||
#open Connection.php | |||
#add a line case '9.4': return 'Postgres94'; break; at the // Detect version and choose appropriate database driver switch. | |||
#open Postgres.php and copy functions getTablespaces + getTablespace | |||
#open Postgres94.php and paste the functions into the class | |||
## replace ", spclocation," with ", pg_tablespace_location(oid) as spclocation," in both functions. | |||
## change class name to Postgres94 | |||
==PostgreSQLStudio == | |||
http://www.postgresqlstudio.org/download/ | |||
==TeamPostgreSQL== | |||
http://www.teampostgresql.com/ |
Latest revision as of 20:01, 6 July 2015
Command Line Tools
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" psql -t -A -h <hostname> <database> <utente> -c "SELECT myquery FROM table";
- script to compare the row count on all tables between a master and a replica database (run in the replica with "trusted" user)
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
Configuration
- some standard locations
- home:/var/lib/postgres
- conf:/etc/postgres/x.x/main/
- logs:/var/log/postgres/x.x/main
- More Logging
add in postgresql.conf
log_line_prefix = '%t %c %u ' # time sessionid user log_statement = 'all'
PostgreSQL GUI's
phpPgAdmin
- fix problem with Postgres version 9.4
- 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 )
- cd classes/database
- copy Postgres84.php to Postgres94.php
- open Connection.php
- add a line case '9.4': return 'Postgres94'; break; at the // Detect version and choose appropriate database driver switch.
- open Postgres.php and copy functions getTablespaces + getTablespace
- open Postgres94.php and paste the functions into the class
- replace ", spclocation," with ", pg_tablespace_location(oid) as spclocation," in both functions.
- change class name to Postgres94
PostgreSQLStudio
http://www.postgresqlstudio.org/download/