postgres stuff

postgres.stuff

sudo su - postgres
psql
\l - list databases
\c db_name - connect to database
ALTER DATABASE db RENAME TO newdb; - rename database
\dt - list tables
\d+ tablename - list fields for table
\du - list users
CREATE DATABASE dbname;
DROP DATABASE [IF EXISTS] name;
ALTER TABLE funz DROP COLUMN col_name;
DROP EXTENSION fuzzystrmatch;

sudo su - postgres
psql -d db_name
grant usage on schema public to public;
grant create on schema public to public;
ALTER USER your_user_name WITH SUPERUSER;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user_name;
CREATE ROLE your_user_name WITH LOGIN PASSWORD 'password';
ALTER ROLE your_user_name createdb;

#copy a database
CREATE DATABASE target_db_name WITH TEMPLATE source_db_name OWNER db_user_name;
#or from command line
pg_dump old_database_name | psql new_database_name