Linux Info: postgres

Showing posts with label postgres. Show all posts

Friday, July 24, 2015

Postgres Query: Given an index find associated table

select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname), ', ') as column_names
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
and i.relname = 'Index_to_search_for'
group by
t.relname,
i.relname
order by
t.relname,
i.relname;

Source:
http://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql

Thursday, September 18, 2014

Postgres As Docker

CONTAINER_NAME="postgres_server"
CONTAINER_DATA_DIR="/var/lib/postgresql/data"
HOST_PORT="55432"
POSTGRES_DOCKER_DIR=`pwd`
HOST_DATA_DIR="${POSTGRES_DOCKER_DIR}/data-dir/"

if [ ! -d "${HOST_DATA_DIR}" ]
then
mkdir -p "${HOST_DATA_DIR}"
fi

sudo docker run --rm --name ${CONTAINER_NAME} \
-v ${HOST_DATA_DIR}:${CONTAINER_DATA_DIR} \
-p ${HOST_PORT}:5432 \
-it postgres $1

Wednesday, May 15, 2013

Postgres Configuration on RedHat

This will install Postgresql on a RedHat 6.4 or CentOS 6.4 system and will allow password-protected remote login access.

# Open the firewall to allow remote postgres connections
sudo /sbin/iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

sudo /sbin/service iptables save
sudo /sbin/service iptables restart

# Install packages through yum
sudo yum install vim-enhanced
sudo yum install postgresql-server

# Initialize a new database directory
sudo service postgresql initdb

# Start the database server
sudo service postgresql start

# Add postgres to the default runlevel
sudo chkconfig postgresql on
sudo chkconfig postgresql --list
sudo chmod 755 /home/idcuser/

# Edit the postgres.conf to allow connections from all ips

sudo vim /var/lib/pgsql/data/postgres.conf
listen_addresses="*"

# Edit the pg_hba.conf file to allow password auth for any ip

sudo vim /var/lib/pgsql/data/pg_hba.conf
host all all 0.0.0.0/0 md5

# Restart postgres
sudo /etc/init.d/postgres restart

# Configure a postgresql superuser and new schema.
sudo -u postgres psql
psql> create role idcuser superuser createdb createrole inherit login;
psql> alter user idcuser with password 'password';
psql> create database sample;
psql> \c sample
psql> create schema sample;
psql> alter user idcuser set search_path to sample,public;

Thursday, March 01, 2012

Finding table sizes in postgres

Finding table sizes in postgres

Taken from: http://wiki.postgresql.org/wiki/Disk_Usage

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Sunday, June 20, 2010

Postgresql Basic Commands

Login to postgresql:
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W
psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection

Default Admin Login:
sudo -u postgres psql -U postgres
sudo -u postgres psql

List databases on postgresql server:
psql -l [-U myuser] [-W]

Turn off line pager pagination in psql:
\pset pager

Determine system tables:
select * from pg_tables where tableowner = 'postgres';

List databases from within a pg shell:
\l

List databases from UNIX command prompt:
psql -U postgres -l

Describe a table:
\d tablename

Quit psql:
\q

Switch postgres database within admin login shell:
\connect databasename

Reset a user password as admin:
alter user usertochange with password 'new_passwd';

Show all tables:
\dt

List all Schemas:
\dn

List all users:
\du

Load data into posgresql:
psql -W -U username -H hostname < file.sql

Dump (Backup) Data into file:
pg_dump -W -U username -h hostname database_name > file.sql

Increment a sequence:
SELECT nextval('my_id_seq');

Create new user:
CREATE USER jjasinski WITH PASSWORD 'myPassword';
# or
sudo -u postgres createuser jjasinski -W

Change user password:
ALTER USER Postgres WITH PASSWORD 'mypass';

Grant user createdb privilege:
ALTER USER myuser WITH createdb;

Create a superuser user:
create user mysuper with password '1234' SUPERUSER
# or even better
create user mysuper with password '1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;
# or
sudo -u postgres createuser jjasinski -W -s

Upgrade an existing user to superuser:
alter user mysuper with superuser;
# or even better
alter user mysuper with SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION

Show Database Version:
SELECT version();

Change Database Owner:
alter database database_name owner to new_owner;

Copy a database:
CREATE DATABASE newdb WITH TEMPLATE originaldb;
http://www.commandprompt.com/ppbook/x14316

View Database Connections:
SELECT * FROM pg_stat_activity;

View show data directory (works on 9.1+; not on 7.x):
show data_directory;

Show run-time parameters:

show all;
select * from pg_settings;

Show the block size setting:
# show block_size;
block_size
------------
8192
(1 row)

Show stored procedure source:
SELECT prosrc FROM pg_proc WHERE proname = 'procname'

Grant examples:
# readonly to all tables for myuser
grant select on all tables in schema public to myuser;
# all privileges on table1 and table2 to myuser
grant all privileges on table1, table2, table3 to myuser;

Restore Postgres .dump file:
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
source

Find all active sessions and kill them (i.e. for when needing to drop or rename db)
Source: http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it

# Postgres 9.2 and above
SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE pg_stat_activity.datname = 'TARGET_DB'

AND pid <> pg_backend_pid();

# Postgres 9.1 and below
SELECT pg_terminate_backend(pg_stat_activity.procpid)