PostgreSQL Handbook (🚧Continuous Update)
1. Init a database sudo su postgres psql create database osmapi create user osmapi \password osmapi grant all privileges on database "osmapi" to osmapi \c osmapi create extension "uuid-ossp" create extension cube create extension earthdistance 2. Duplicate a table create table dupe_users as (select * from users); create table dupe_users as (select * from users) with no data; 3. Change the owner of a table and view ALTER TABLE public.tablename OWNER TO my_user; ALTER VIEW latest_status_logs OWNER TO my_user; 4. Drop a column ALTER TABLE tablename DROP COLUMN category_id; 5. Install “uuid-ossp” apt-get install postgresql-contrib-9.3 6. Check index SELECT * FROM pg_indexes WHERE TABLENAME NOT LIKE 'pg%' DROP INDEX xxx alter table activity_auditings drop constraint activity_auditings_extra_curriculum_no_key 7. Connect to the remote database by CLI psql -d mydb -U myuser psql -h myhost -d mydb -U myuser psql --host=dataant-test.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \ --port=5432 \ --username=dataanttest \ --password \ --dbname=dataantapi 8. Change a column name ALTER TABLE client_profiles RENAME COLUMN user_id TO profile_id 9. Install Postgres sudo apt-get update sudo apt-get install postgresql postgresql-contrib 10. Set the access permission of other machines /etc/postgresql/9.1/main/pg_hba.conf # ipv4 host all all 0.0.0.0/0 md5 (e.g. 192.168.0.0/16) host all all 192.168.239.133/16 md5 /etc/postgresql/9.1/main/postgresql.conf listen_address='*' Restart database ...
