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
sudo service postgresql restart
11. Change user password
\password osmapi
12. List all users
\list
13. Set UTF8 to WIN1252
set client_encoding to 'WIN1252'
14. Check the privileges inside psql
\dn+ public
15. Rebuild schema
drop schema public cascade
create schema public
grant usage on schema public to public
grant create on schema public to public
16. Copy one column to another column
update table set columnB = columnA
17. How to find the data directory
Connect to a database
SHOW data_directory;
18. Uninstall PostgreSQL
sudo dpkg -l | grep postgres
sudo apt-get --purge remove postgresql postgresql-doc postgresql-common
sudo rm -Rf /etc/postgresql /var/lib/postgresql
sudo apt-get install postgresql
19. Change a table name
ALTER TABLE table_name RENAME TO new_table_name
20. Add auto incremental primary key
ALTER TABLE public.extra_curriculums
ALTER COLUMN id TYPE integer;
ALTER TABLE public.extra_curriculums
ADD PRIMARY KEY (id);
ALTER TABLE extra_curriculums
ALTER COLUMN id SET DEFAULT nextval('extra_curriculums_pkey'::regclass);
21. Reset sequence
SELECT * FROM information_schema.sequences;
ALTER SEQUENCE extra_curriculums_id_seq RESTART WITH 1231;
22. Grant read for all users
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dataantapi
23. Change a contranit from Not Null to Nullable
ALTER TABLE users ALTER COLUMN password DROP NOT NULL
24. Get and set timezone
SELECT EXTRACT(TIMEZONE FROM now())/3600.0;
SET TIME ZONE 'UTC';
25. List all databases
SELECT datname FROM pg_database WHERE datistemplate = false;
26. Dump a specific table
pg_dump -t <table> <database> > xxx.sql
pg_dump -h wisydb.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \
-U dataantapi \
-t profiles timwlxcss > timwlxapi-profiles1609.sql
27. Query db connection states
SELECT * FROM pg_stat_activity;
28. Only export the specific database’s tables’ schema
pg_dump -h prerelease.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \
-U dataantapi \
-s wisydb > wisydb1611.sql
29. Export the database’s tables’ data
pg_dump -h prerelease.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \
-U dataantapi timwlx \
-f timwlxapi2805.sql
30. Import the database’s tables’ data
psql -h 192.168.226.129 -U <table> <database> < xxx.sql
If this post helped you to solve a problem or provided you with new insights, please upvote it and share your experience in the comments below. Your comments can help others who may be facing similar challenges. Thank you!