Postgres dump and restore a single table

To dump and restore a single table in Postgres in SQL format use something similar to:

pg_dump -U[user] -n[schema] -C -d  -t[table]  [dbname] > db_output.sql

where

• -n Schema name
• -d generate INSERT statements
• -C create a schema definition
• -t Table name

And to restore:

psql -U[user name] [db_name]  < db_output.sql

Other useful options:

• -s Schema only

Postgres and full text search

Some modules that add full text search to the core engine

Postgres 8.3 has native full text search. See documentation. Looks like it might be limited / slow for large volumes.

TSearch2 doesn’t look like it’s been touched in four or five years

pgestraier provides Hyper Estraier access for Postgres. Looks like it may be a little stale.

OpenFTS – open-source full text search based on Postgres native full text search – last activity in 2005, most well before that.

Sphinx is an open-source SQL -based full text engine under active development.

SQL to add/revoke permissions

To add/revoke permissions for all tables in a database:


-- to create the role
CREATE ROLE read_only_role_foo LOGIN PASSWORD 'password' NOINHERIT
VALID UNTIL 'infinity';
grant usage on schema public to read_only_role_foo;
select 'GRANT SELECT ON ' || c.relname || ' TO read_only_role_foo;'
from pg_class AS c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname NOT IN('pg_catalog', 'pg_toast')
and pg_table_is_visible(c.oid);



-- to drop the role
select 'REVOKE SELECT ON ' || c.relname || ' FROM read_only_role_foo;'
from pg_class AS c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname NOT IN('pg_catalog', 'pg_toast')
and pg_table_is_visible(c.oid);
REVOKE usage ON schema public FROM read_only_role_foo;
DROP ROLE read_only_role_foo;


JDBC URL – Postgres example

Download the postgresql*.jar file and place it somewhere it will be loaded as a JAR file

jdbc:postgresql://host:port/database

db.url=jdbc:postgresql://localhost:5432/my_database_name
db.driver=org.postgresql.Driver
db.user=me
db.password=mypassword

Postgres – Dumping and restoring a database

To dump a database

pg_dump -Fc -Uuser -d mydb > db.dump

To create schema and make insert statements

pg_dump  -Uuser -d mydb –create –inserts > db.sql

To restore a database

pg_restore -Uuser -d mydb db.dump

Patch for Java Timestamp problems in Postgres

See http://archives.postgresql.org/pgsql-patches/2001-01/msg00028.php