Friday, November 11, 2011

Postgresql - Move a set of tables from one schema to another | Cheat sheet

Of course, the destination schema must exist first =-).

And then thanks to our friend the "ALTER TABLE" command you can issue something like:

General structure
ALTER TABLE myschema.mytable SET SCHEMA yourschema;
[sourcecode language="sql"]

musicbrainz_db=# select 'alter table dw.'|| tablelist.tablename || ' set schema musicbrainz ; ' from (select tablename from pg_tables where schemaname = 'dw' and tablename not like 'pga%') as tablelist ;


The only trick here ,is knowing that  the catalog of tables resides in the view pg_catalog.pg_tables.


As a foot note, I found this pretty useful cheat sheet for postgresql: I reproduce here as a backup:


Create the filesystem

$ export PGROOT="/var/lib/postgres"
$ mkdir -p $PGROOT/data && chown postgres.postgres $PGROOT/data
$ su - postgres -c "/usr/bin/initdb -D $PGROOT/data"

Make it UTF8 by default:

$ su - postgres -c "/usr/bin/initdb -E utf8 --locale=en_US.UTF-8 \

Create Databases

$ createdb -O owner -T some_template database_name

By default, PostgreSQL listens on TCP port 5432.

Dump all databases

pg_dumpall --clean > databases.sql
Dump a database with compression (-Fc)

pg_dump -Fc --file=database.sql --clean database
Dump a table

pg_dump [-d database] [--schema schema] -t table
Dump a table definition (no data)

pg_dump -s [-d database] [--schema schema] -t table
Restore a database from a dump file

pg_restore -Fc database.sql
Start the PostgreSQL interactive terminal

Reset password of postgres user
# su postgres
# psql -d template1


Psql - show a list of databases
=> \l
Lowercase L, not the number 1
Psql - show all users
=> select * from pg_user;
Psql - show all tables (including system tables)
=> select * from pg_tables;
Psql - show tables in the current context (database/schema)
=> \d
Psql - change current database
=> \c database;
Psql - show all schemas in the current database
=> \dn
Psql - Grant permissions on a schema to a user
=> GRANT ALL ON myschema TO user;
Psql - quit psql
=> \q
Psql - show help
=> \?
Psql - copy a table to a tab delimeted file
=> COPY table TO 'table.txt';
Psql - load a table from a tab delimeted file
=> COPY table FROM 'table.txt';
Psql - show permissions on database objects
=> \z [object]
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
x -- REFERENCES (foreign keys)
arwdRxt -- ALL PRIVILEGES (for tables)
* -- grant option for preceding privilege
/yyyy -- user who granted this privilege
Run the vacuum utility
=> vacuumdb --verbose --analyze --all
Note: vacuum reclaims space from deleted records and updates indexes. It should
be set up in cron. Newer versions of postgresql may run vacuum automatically.
Increase perfomance with shared memory
One effective performance tuning tip for Postgresql is to increase the shared
memory buffers. This might require adding RAM to the server. Many Linux distros
default to 32MB of shared memory, controlled by two kernel parameters:

These values can be changed at run time, but it is better to set them at boot
using the /etc/sysctl.conf file. This increases shared memory to 1GB:
# increase shared buffers for postgres at boot
Then, tell PostgreSQL to use 768MB of the 1GB available in the
/var/lib/pgsql/data/postgresql.conf file:
shared_buffers = 98304 # min 16, at least max_connections*2, 8KB each

Restart PostgreSQL for the change to take effect.

brew install postgresql

To build plpython against a specific Python, set PYTHON prior to brewing:
PYTHON=/usr/local/bin/python brew install postgresql

If this is your first install, create a database with:
initdb /usr/local/var/postgres

Automatically load on login with:
launchctl load -w

and if that fails try the homebrew mysql launchd approach:
cp /usr/local/Cellar/postgresql/8.4.4/org.postgresql.postgres.plist
launchctl load ~/Library/LaunchAgents/org.postgresql.postgres.plist

Or start manually with:
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log

And stop with:
pg_ctl -D /usr/local/var/postgres stop -s -m fast

If you want to install the postgres gem, including ARCHFLAGS is recommended:
env ARCHFLAGS="-arch x86_64" gem install pg

PostgreSQL Examples

Adding new user called BRIAN:
$ sudo su - postgres
$ createuser --createdb --username postgres --no-createrole \
--pwprompt BRIAN
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
$ exit


--Add a unique constraint to the email column in the customer table
ALTER TABLE customer ADD CONSTRAINT customer_email_key UNIQUE (email);

No comments:

Post a Comment