https://help.ubuntu.com/community/PostgreSQL
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
Set:
listen_addresses = '*'
sudo -u postgres psql postgres
\password postgres
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
Passwords are stored in ~/.pgpass
.
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')
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:postgresql://localhost:5432/database
org.postgresql.Driver