common_postgres.md

Common Postgres Things

Create

Create new user

CREATE USER $user_name$;

CREATE USER $user_name$ WITH PASSWORD '$password$';

Create new database

CREATE DATABASE $new_db_name$;

CREATE DATABASE $new_db_name$ WITH TEMPLATE $template_db$;

or

createdb $new_db_name$

createdb $new_db_name$ --template $template_db$

Alter

Change Database Owner

ALTER DATABASE $database_name$ OWNER TO $owner_name$;

Change Table Owner

ALTER TABLE $table_name$ OWNER TO $owner_name$;

Delete

Delete data from table

DELETE FROM $table_name$ ;

TRUNCATE $table_name$;

Note: TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

Backup & Restore

Backup from databse

pg_dump --verbose --format custom --file=filename.pgdump --dbname=$db_name$

Restore to database

pg_restore --dbname $database_name$ filename.pgdump

pg_restore --schema-only --dbname $database_name$ filename.pgdump