Postgresql Tips and Tricks

postgresql_tips.md

Initial Configuration

https://help.ubuntu.com/community/PostgreSQL

Basic server setup

pg_hba.conf

change from

# Database administrative login by Unix domain socket
local   all             postgres                                peer

to

# Database administrative login by Unix domain socket
local   all             postgres                                md5

add e.g.:

host    all             all             192.168.0.0/24          md5

postgresql.conf

Set:

listen_addresses = '*'

Password

sudo -u postgres psql postgres
\password postgres

Dump and restore

Dump from localhost: pg_dump -U postgres -W -d mydbname -h localhost > mydbname_postgresql.dump

(or for older versions: pg_dump -U postgres -W -h localhost mydbname > mydbname_postgresql.dump)

Restore to a remote machine: psql -h kaste1 -U postgres -W -d mydbname < mydbname_postgresql.dump

PgAdmin3

Passwords are stored in ~/.pgpass.

SQL

Rename Columns to Lowercase

First, create a function:

CREATE OR REPLACE FUNCTION lowercase_column_names(
    IN tname TEXT     -- tablename to alter
) 
RETURNS boolean 
LANGUAGE plpgsql 
AS
$$
DECLARE
    row RECORD;
BEGIN   
    FOR row IN SELECT column_name FROM information_schema.columns WHERE table_name = tname LOOP
	IF row.column_name != lower(row.column_name) THEN
		EXECUTE 'ALTER TABLE ' || tname || ' RENAME COLUMN ' || quote_ident(row.column_name) || ' TO ' || lower(row.column_name) || ';';
	END IF;
    END LOOP;
    RETURN TRUE;
END;
$$

and then use it:

select lowercase_column_names('customer')

Moving tables

List all tables with names ending with "_full":

COPY 
	(SELECT distinct table_name FROM information_schema.columns where table_name like '%_full')
TO
	STDOUT DELIMITER ',';

alternatively: COPY ... TO '/tmp/tables.csv'

Export all given tables:

export PGPASSWORD='password'
while read table ;do pg_dump -U postgres  -t $table crunchbase > $table.sql ; done < tables.csv

Restore:

for i in *.sql ;do psql -U postgres -d crunchbase_full < $i ;done

JDBC

jdbc:postgresql://localhost:5432/database

org.postgresql.Driver