nnasoody · November 30, 2017 02:26 · Jul 14, 2016 · May 4, 2016 · Apr 28, 2016 · Apr 28, 2016
diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -65,6 +65,63 @@ sudo tail -24 $(find /var/log/postgresql -name 'postgresql-*-main.log')
 
 
 
+## Recon
+
+##### show version
+```
+SHOW SERVER_VERSION;
+```
+
+##### show system status
+```sql
+\conninfo
+```
+
+##### show environmental variables
+```sql
+SHOW ALL;
+```
+
+##### list users
+```sql
+SELECT rolname FROM pg_roles;
+```
+
+##### show current user
+```sql
+SELECT current_user;
+```
+
+##### show current user's permissions
+```
+\du
+```
+
+##### list databases
+```sql
+\l
+```
+
+##### show current database
+```sql
+SELECT current_database();
+```
+
+##### show all tables in database
+```sql
+\dt
+```
+
+##### list functions
+```sql
+\df <schema>
+```
+<br/><br/><br/>
+
+
+
+
+
 ## Databases
 
 ##### list databasees

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -356,23 +356,30 @@ WHERE <column_name> = <value>;
 
 ## Scripting
 
-##### run local script
+##### run local script, on remote host
 http://www.postgresql.org/docs/current/static/app-psql.html
 ```shell
 psql -U <username> -d <database> -h <host> -f <local_file>
 
 psql --username=<username> --dbname=<database> --host=<host> --file=<local_file>
 ```
 
-##### backup database data
+##### backup database data, everything
 http://www.postgresql.org/docs/current/static/app-pgdump.html
 ```shell
+pg_dump <database_name>
+
+pg_dump <database_name>
+```
+
+##### backup database, only data
+```shell
 pg_dump -a <database_name>
 
 pg_dump --data-only <database_name>
 ```
 
-##### backup database schema
+##### backup database, only schema
 ```shell
 pg_dump -s <database_name>
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -323,23 +323,28 @@ SELECT * FROM <table_name> LIMIT 1;
 SELECT * FROM <table_name> WHERE <column_name> = <value>;
 ```
 
-##### add / create / insert table into table
+##### insert data
 http://www.postgresql.org/docs/current/static/sql-insert.html
 ```sql
 INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
 ```
 
-##### edit / change / update data
+##### edit data
 http://www.postgresql.org/docs/current/static/sql-update.html
 ```sql
 UPDATE <table_name>
 SET <column_1> = <value_1>, <column_2> = <value_2>
 WHERE <column_1> = <value>;
 ```
 
-##### remove / delete data
+##### delete all data
 http://www.postgresql.org/docs/current/static/sql-delete.html
 ```sql
+DELETE FROM <table_name>;
+```
+
+##### delete specific data
+```sql
 DELETE FROM <table_name>
 WHERE <column_name> = <value>;
 ```

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -257,6 +257,13 @@ http://www.postgresql.org/docs/current/static/sql-droptable.html
 ```sql
 DROP TABLE IF EXISTS <table_name> CASCADE;
 ```
+<br/><br/><br/>
+
+
+
+
+
+## Columns
 
 ##### add column
 http://www.postgresql.org/docs/current/static/sql-altertable.html

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -92,6 +92,12 @@ http://www.postgresql.org/docs/current/static/sql-dropdatabase.html
 ```sql
 DROP DATABASE IF EXISTS <database_name>;
 ```
+
+##### rename database
+http://www.postgresql.org/docs/current/static/sql-alterdatabase.html
+```sql
+ALTER DATABASE <old_name> RENAME TO <new_name>;
+```
 <br/><br/><br/>
 
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -77,6 +77,11 @@ sudo tail -24 $(find /var/log/postgresql -name 'postgresql-*-main.log')
 \c <database_name>
 ```
 
+##### show current database
+```sql
+SELECT current_database();
+```
+
 ##### create database
 http://www.postgresql.org/docs/current/static/sql-createdatabase.html
 ```sql

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -29,20 +29,20 @@ psql -U <username> -d <database> -h <hostname>
 psql --username=<username> --dbname=<database> --host=<hostname>
 ```
 
-##### info
+##### disconnect
 ```sql
-\conninfo
+\q
+\!
 ```
 
 ##### clear the screen
 ```sql
 (CTRL + L)
 ```
 
-##### disconnect
+##### info
 ```sql
-\q
-\!
+\conninfo
 ```
 
 ##### configure

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -5,7 +5,7 @@ This is a collection of the most common commands I run while administering Postg
 
 ## Setup
 
-##### installation
+##### installation, Ubuntu
 http://www.postgresql.org/download/linux/ubuntu/
 https://help.ubuntu.com/community/PostgreSQL
 ``` shell

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -54,7 +54,7 @@ sudo nano $(locate -l 1 main/postgresql.conf)
 sudo service postgresql restart
 ```
 
-##### debug
+##### debug logs
 ```shell
 # print the last 24 lines of the debug log
 sudo tail -24 $(find /var/log/postgresql -name 'postgresql-*-main.log')

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -50,7 +50,14 @@ psql --username=<username> --dbname=<database> --host=<hostname>
 http://www.postgresql.org/docs/current/static/runtime-config.html
 
 ```shell
-sudo nano $(locate main/postgresql.conf)
+sudo nano $(locate -l 1 main/postgresql.conf)
+sudo service postgresql restart
+```
+
+##### debug
+```shell
+# print the last 24 lines of the debug log
+sudo tail -24 $(find /var/log/postgresql -name 'postgresql-*-main.log')
 ```
 <br/><br/><br/>
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -44,6 +44,14 @@ psql --username=<username> --dbname=<database> --host=<hostname>
 \q
 \!
 ```
+
+##### configure
+
+http://www.postgresql.org/docs/current/static/runtime-config.html
+
+```shell
+sudo nano $(locate main/postgresql.conf)
+```
 <br/><br/><br/>
 
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -62,7 +62,7 @@ psql --username=<username> --dbname=<database> --host=<hostname>
 \c <database_name>
 ```
 
-##### create datacase
+##### create database
 http://www.postgresql.org/docs/current/static/sql-createdatabase.html
 ```sql
 CREATE DATABASE <database_name> WITH OWNER <username>;

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -319,8 +319,11 @@ WHERE <column_name> = <value>;
 ## Scripting
 
 ##### run local script
+http://www.postgresql.org/docs/current/static/app-psql.html
 ```shell
 psql -U <username> -d <database> -h <host> -f <local_file>
+
+psql --username=<username> --dbname=<database> --host=<host> --file=<local_file>
 ```
 
 ##### backup database data
@@ -341,42 +344,38 @@ pg_dump --schema-only <database_name>
 ##### restore database data
 http://www.postgresql.org/docs/current/static/app-pgrestore.html
 ```shell
-pg_restore -a <database_name> <file_pathway>
+pg_restore -d <database_name> -a <file_pathway>
+
+pg_restore --dbname=<database_name> --data-only <file_pathway>
 ```
 
 ##### restore database schema
 ```shell
-pg_restore -d <database_name> <file_pathway>
+pg_restore -d <database_name> -s <file_pathway>
+
+pg_restore --dbname=<database_name> --schema-only <file_pathway>
 ```
 
 ##### export table into CSV file
 http://www.postgresql.org/docs/current/static/sql-copy.html
 ```sql
-COPY <table_name> TO '<file_path>' DELIMITER ',' CSV;
-
-\copy <table_name> TO '<file_path>' DELIMITER ',' CSV
+\copy <table_name> TO '<file_path>' CSV
 ```
 
 ##### export table, only specific columns, to CSV file
 ```sql
-COPY <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER ',' CSV;
-
-\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER ',' CSV
+\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' CSV
 ```
 
 ##### import CSV file into table
 http://www.postgresql.org/docs/current/static/sql-copy.html
 ```sql
-COPY <table_name> FROM '<file_path>' DELIMITER ',' CSV;
-
-\copy <table_name> FROM '<file_path>' DELIMITER ',' CSV
+\copy <table_name> FROM '<file_path>' CSV
 ```
 
 ##### import CSV file into table, only specific columns
 ```sql
-COPY <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV;
-
-\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV
+\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' CSV
 ```
 <br/><br/><br/>
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -1,6 +1,6 @@
 # Postgres Cheatsheet
 
-This is a collection of the most common commands I run while administering Postgres databases.
+This is a collection of the most common commands I run while administering Postgres databases. The variables shown between the open and closed tags, "<" and ">", should be replaced with a name you choose. Postgres has multiple shortcut functions, starting with a forward slash, "\". Any SQL command that is not a shortcut, must end with a semicolon, ";". You can use the keyboard UP and DOWN keys to scroll the history of previous commands you've run.
 
 
 ## Setup

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -109,6 +109,12 @@ ALTER ROLE <user_name> WITH PASSWORD '<password>';
 
 ## Permissions
 
+##### become the postgres user, if you have permission errors
+```shell
+sudo su - postgres
+psql
+```
+
 ##### grant all permissions on database
 http://www.postgresql.org/docs/current/static/sql-grant.html
 ```sql
@@ -213,6 +219,13 @@ CREATE TABLE <table_name>(
 );
 ```
 
+##### create table, with an auto-incrementing primary key
+```sql
+CREATE TABLE <table_name> (
+  <column_name> SERIAL PRIMARY KEY
+);
+```
+
 ##### delete table
 http://www.postgresql.org/docs/current/static/sql-droptable.html
 ```sql
@@ -238,13 +251,6 @@ ALTER TABLE <table_name> IF EXISTS
 DROP <column_name>;
 ```
 
-##### create auto-incrementing primary key
-```sql
-CREATE TABLE <table_name> (
-  <column_name> SERIAL PRIMARY KEY
-);
-```
-
 ##### update column to be an auto-incrementing primary key
 ```sql
 ALTER TABLE <table_name>

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -1,4 +1,4 @@
-# Posgres Cheatsheet
+# Postgres Cheatsheet
 
 This is a collection of the most common commands I run while administering Postgres databases.
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -390,5 +390,5 @@ http://www.postgresql.org/docs/current/static/runtime-config-logging.html
 
 
 ## Advanced Features
-http://www.tutorialspoint.com/postgresql/postgresql_indexes.htm
+http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm
 
diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -376,6 +376,19 @@ COPY <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER
 
 
 
+
+
+## Debugging
+
+http://www.postgresql.org/docs/current/static/using-explain.html
+
+http://www.postgresql.org/docs/current/static/runtime-config-logging.html
+<br/><br/><br/>
+
+
+
+
+
 ## Advanced Features
 http://www.tutorialspoint.com/postgresql/postgresql_indexes.htm
 
diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -34,6 +34,11 @@ psql --username=<username> --dbname=<database> --host=<hostname>
 \conninfo
 ```
 
+##### clear the screen
+```sql
+(CTRL + L)
+```
+
 ##### disconnect
 ```sql
 \q

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -20,11 +20,13 @@ psql
 ```
 
 ##### connect
+http://www.postgresql.org/docs/current/static/app-psql.html
 ```sql
-@see http://www.postgresql.org/docs/9.2/static/app-psql.html
-psql --username=<username> --dbname=<database> --host=<hostname>
+psql
 
 psql -U <username> -d <database> -h <hostname>
+
+psql --username=<username> --dbname=<database> --host=<hostname>
 ```
 
 ##### info
@@ -336,14 +338,6 @@ pg_restore -a <database_name> <file_pathway>
 pg_restore -d <database_name> <file_pathway>
 ```
 
-##### import CSV file into table
-http://www.postgresql.org/docs/current/static/sql-copy.html
-```sql
-COPY <table_name> FROM '<file_path>' DELIMITER ',' CSV;
-
-\copy <table_name> FROM '<file_path>' DELIMITER ',' CSV
-```
-
 ##### export table into CSV file
 http://www.postgresql.org/docs/current/static/sql-copy.html
 ```sql
@@ -359,9 +353,24 @@ COPY <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER '
 \copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER ',' CSV
 ```
 
+##### import CSV file into table
+http://www.postgresql.org/docs/current/static/sql-copy.html
+```sql
+COPY <table_name> FROM '<file_path>' DELIMITER ',' CSV;
+
+\copy <table_name> FROM '<file_path>' DELIMITER ',' CSV
+```
+
 ##### import CSV file into table, only specific columns
 ```sql
 COPY <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV;
 
 \copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV
 ```
+<br/><br/><br/>
+
+
+
+## Advanced Features
+http://www.tutorialspoint.com/postgresql/postgresql_indexes.htm
+
diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -246,7 +246,11 @@ ADD COLUMN <column_name> SERIAL PRIMARY KEY;
 
 ##### insert into a table, with an auto-incrementing primary key
 ```sql
-INSERT INTO <table_name> (column1_name,<column2_name>)
+INSERT INTO <table_name>
+VALUES (DEFAULT, <value1>);
+
+
+INSERT INTO <table_name> (<column1_name>,<column2_name>)
 VALUES ( <value1>,<value2> );
 ```
 <br/><br/><br/>

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -230,6 +230,25 @@ ALTER <column_name> TYPE <data_type> [<constraints>];
 ALTER TABLE <table_name> IF EXISTS
 DROP <column_name>;
 ```
+
+##### create auto-incrementing primary key
+```sql
+CREATE TABLE <table_name> (
+  <column_name> SERIAL PRIMARY KEY
+);
+```
+
+##### update column to be an auto-incrementing primary key
+```sql
+ALTER TABLE <table_name>
+ADD COLUMN <column_name> SERIAL PRIMARY KEY;
+```
+
+##### insert into a table, with an auto-incrementing primary key
+```sql
+INSERT INTO <table_name> (column1_name,<column2_name>)
+VALUES ( <value1>,<value2> );
+```
 <br/><br/><br/>
 
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -189,6 +189,7 @@ SELECT * FROM pg_catalog.pg_tables
 
 ##### list table schema
 ```sql
+\d <table_name>
 \d+ <table_name>
 
 SELECT column_name, data_type, character_maximum_length
@@ -205,11 +206,6 @@ CREATE TABLE <table_name>(
 );
 ```
 
-##### list table schema
-```sql
-\d <table_name>
-```
-
 ##### delete table
 http://www.postgresql.org/docs/current/static/sql-droptable.html
 ```sql
@@ -234,8 +230,6 @@ ALTER <column_name> TYPE <data_type> [<constraints>];
 ALTER TABLE <table_name> IF EXISTS
 DROP <column_name>;
 ```
-
-
 <br/><br/><br/>
 
 
@@ -244,17 +238,12 @@ DROP <column_name>;
 
 ## Data
 
-##### add / create / insert table into table
-http://www.postgresql.org/docs/current/static/sql-insert.html
-```sql
-INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
-```
-
 ##### read all data
 http://www.postgresql.org/docs/current/static/sql-select.html
 ```sql
 SELECT * FROM <table_name>;
 ```
+
 ##### read one row of data
 ```sql
 SELECT * FROM <table_name> LIMIT 1;
@@ -265,15 +254,21 @@ SELECT * FROM <table_name> LIMIT 1;
 SELECT * FROM <table_name> WHERE <column_name> = <value>;
 ```
 
-##### update / edit data
+##### add / create / insert table into table
+http://www.postgresql.org/docs/current/static/sql-insert.html
+```sql
+INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
+```
+
+##### edit / change / update data
 http://www.postgresql.org/docs/current/static/sql-update.html
 ```sql
 UPDATE <table_name>
 SET <column_1> = <value_1>, <column_2> = <value_2>
 WHERE <column_1> = <value>;
 ```
 
-##### delete data
+##### remove / delete data
 http://www.postgresql.org/docs/current/static/sql-delete.html
 ```sql
 DELETE FROM <table_name>
@@ -326,6 +321,21 @@ COPY <table_name> FROM '<file_path>' DELIMITER ',' CSV;
 \copy <table_name> FROM '<file_path>' DELIMITER ',' CSV
 ```
 
+##### export table into CSV file
+http://www.postgresql.org/docs/current/static/sql-copy.html
+```sql
+COPY <table_name> TO '<file_path>' DELIMITER ',' CSV;
+
+\copy <table_name> TO '<file_path>' DELIMITER ',' CSV
+```
+
+##### export table, only specific columns, to CSV file
+```sql
+COPY <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER ',' CSV;
+
+\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER ',' CSV
+```
+
 ##### import CSV file into table, only specific columns
 ```sql
 COPY <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV;

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -288,30 +288,34 @@ WHERE <column_name> = <value>;
 ## Scripting
 
 ##### run local script
-```sql
+```shell
 psql -U <username> -d <database> -h <host> -f <local_file>
 ```
 
 ##### backup database data
 http://www.postgresql.org/docs/current/static/app-pgdump.html
-```sql
+```shell
 pg_dump -a <database_name>
 
 pg_dump --data-only <database_name>
 ```
 
 ##### backup database schema
-```sql
+```shell
 pg_dump -s <database_name>
 
 pg_dump --schema-only <database_name>
 ```
 
 ##### restore database data
+http://www.postgresql.org/docs/current/static/app-pgrestore.html
+```shell
+pg_restore -a <database_name> <file_pathway>
+```
 
 ##### restore database schema
-```sql
-pg_restore -d somedb dump.dmp
+```shell
+pg_restore -d <database_name> <file_pathway>
 ```
 
 ##### import CSV file into table

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -293,17 +293,38 @@ psql -U <username> -d <database> -h <host> -f <local_file>
 ```
 
 ##### backup database data
+http://www.postgresql.org/docs/current/static/app-pgdump.html
+```sql
+pg_dump -a <database_name>
 
-##### backup database schema
+pg_dump --data-only <database_name>
+```
 
-##### backup database functions
+##### backup database schema
+```sql
+pg_dump -s <database_name>
 
-##### backup database views
+pg_dump --schema-only <database_name>
+```
 
 ##### restore database data
 
 ##### restore database schema
+```sql
+pg_restore -d somedb dump.dmp
+```
+
+##### import CSV file into table
+http://www.postgresql.org/docs/current/static/sql-copy.html
+```sql
+COPY <table_name> FROM '<file_path>' DELIMITER ',' CSV;
 
-##### restore database functions
+\copy <table_name> FROM '<file_path>' DELIMITER ',' CSV
+```
 
-##### restore database views
+##### import CSV file into table, only specific columns
+```sql
+COPY <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV;
+
+\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV
+```
diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -9,7 +9,8 @@ This is a collection of the most common commands I run while administering Postg
 http://www.postgresql.org/download/linux/ubuntu/
 https://help.ubuntu.com/community/PostgreSQL
 ``` shell
-sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > /etc/apt/sources.list.d/pgdg.list
+sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > \
+  /etc/apt/sources.list.d/pgdg.list
 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 sudo apt-get update
 sudo apt-get install -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5
@@ -244,11 +245,13 @@ DROP <column_name>;
 ## Data
 
 ##### add / create / insert table into table
+http://www.postgresql.org/docs/current/static/sql-insert.html
 ```sql
 INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
 ```
 
 ##### read all data
+http://www.postgresql.org/docs/current/static/sql-select.html
 ```sql
 SELECT * FROM <table_name>;
 ```
@@ -263,13 +266,15 @@ SELECT * FROM <table_name> WHERE <column_name> = <value>;
 ```
 
 ##### update / edit data
+http://www.postgresql.org/docs/current/static/sql-update.html
 ```sql
 UPDATE <table_name>
 SET <column_1> = <value_1>, <column_2> = <value_2>
 WHERE <column_1> = <value>;
 ```
 
 ##### delete data
+http://www.postgresql.org/docs/current/static/sql-delete.html
 ```sql
 DELETE FROM <table_name>
 WHERE <column_name> = <value>;

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -6,8 +6,8 @@ This is a collection of the most common commands I run while administering Postg
 ## Setup
 
 ##### installation
-@see http://www.postgresql.org/download/linux/ubuntu/
-@see https://help.ubuntu.com/community/PostgreSQL
+http://www.postgresql.org/download/linux/ubuntu/
+https://help.ubuntu.com/community/PostgreSQL
 ``` shell
 sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > /etc/apt/sources.list.d/pgdg.list
 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
@@ -55,10 +55,12 @@ psql -U <username> -d <database> -h <hostname>
 ```
 
 ##### create datacase
+http://www.postgresql.org/docs/current/static/sql-createdatabase.html
 ```sql
 CREATE DATABASE <database_name> WITH OWNER <username>;
 ```
 ##### delete database
+http://www.postgresql.org/docs/current/static/sql-dropdatabase.html
 ```sql
 DROP DATABASE IF EXISTS <database_name>;
 ```
@@ -75,18 +77,20 @@ SELECT rolname FROM pg_roles;
 ```
 
 ##### create user
+http://www.postgresql.org/docs/current/static/sql-createuser.html
 ```sql
-@see http://www.postgresql.org/docs/9.4/static/sql-createuser.html
 CREATE USER <user_name> WITH PASSWORD '<password>';
 ```
+
 ##### drop user
+http://www.postgresql.org/docs/current/static/sql-dropuser.html
 ```sql
-@see http://www.postgresql.org/docs/9.4/static/sql-dropuser.html
 DROP USER IF EXISTS <user_name>;
 ```
+
 ##### alter user password
+http://www.postgresql.org/docs/current/static/sql-alterrole.html
 ```sql
-@see http://www.postgresql.org/docs/9.4/static/sql-alterrole.html
 ALTER ROLE <user_name> WITH PASSWORD '<password>';
 ```
 <br/><br/><br/>
@@ -98,10 +102,11 @@ ALTER ROLE <user_name> WITH PASSWORD '<password>';
 ## Permissions
 
 ##### grant all permissions on database
+http://www.postgresql.org/docs/current/static/sql-grant.html
 ```sql
-@see http://www.postgresql.org/docs/9.4/static/sql-grant.html
 GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;
 ```
+
 ##### grant connection permissions on database
 ```sql
 GRANT CONNECT ON DATABASE <db_name> TO <user_name>;
@@ -116,6 +121,7 @@ GRANT USAGE ON SCHEMA public TO <user_name>;
 ```sql
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;
 ```
+
 ##### grant permissions to select, update, insert, delete, on a all tables
 ```sql
 GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
@@ -125,6 +131,7 @@ GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
 ```sql
 GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;
 ```
+
 ##### grant permissions, to select, on a table
 ```sql
 GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;
@@ -147,13 +154,14 @@ SELECT nspname FROM pg_catalog.pg_namespace;
 ```
 
 #####  create schema
+http://www.postgresql.org/docs/current/static/sql-createschema.html
 ```sql
-@see http://www.postgresql.org/docs/9.3/static/sql-createschema.html
 CREATE SCHEMA IF NOT EXISTS <schema_name>;
 ```
+
 #####  drop schema
+http://www.postgresql.org/docs/current/static/sql-dropschema.html
 ```sql
-@see http://www.postgresql.org/docs/9.4/static/sql-dropschema.html
 DROP SCHEMA IF EXISTS <schema_name> CASCADE;
 ```
 <br/><br/><br/>
@@ -188,7 +196,7 @@ WHERE table_name = '<table_name>';
 ```
 
 ##### create table
-@see http://www.postgresql.org/docs/9.4/static/sql-createtable.html
+http://www.postgresql.org/docs/current/static/sql-createtable.html
 ```sql
 CREATE TABLE <table_name>(
   <column_name> <column_type>,
@@ -202,10 +210,31 @@ CREATE TABLE <table_name>(
 ```
 
 ##### delete table
-@see http://www.postgresql.org/docs/9.4/static/sql-droptable.html
+http://www.postgresql.org/docs/current/static/sql-droptable.html
 ```sql
 DROP TABLE IF EXISTS <table_name> CASCADE;
 ```
+
+##### add column
+http://www.postgresql.org/docs/current/static/sql-altertable.html
+```sql
+ALTER TABLE <table_name> IF EXISTS
+ADD <column_name> <data_type> [<constraints>];
+```
+
+##### update column
+```sql
+ALTER TABLE <table_name> IF EXISTS
+ALTER <column_name> TYPE <data_type> [<constraints>];
+```
+
+##### delete column
+```sql
+ALTER TABLE <table_name> IF EXISTS
+DROP <column_name>;
+```
+
+
 <br/><br/><br/>
 
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -2,6 +2,9 @@
 
 This is a collection of the most common commands I run while administering Postgres databases.
 
+
+## Setup
+
 ##### installation
 @see http://www.postgresql.org/download/linux/ubuntu/
 @see https://help.ubuntu.com/community/PostgreSQL
@@ -14,11 +17,6 @@ sudo apt-get install -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-
 sudo su - postgres
 psql
 ```
-<br/><br/><br/>
-
-
-
-
 
 ##### connect
 ```sql
@@ -27,6 +25,17 @@ psql --username=<username> --dbname=<database> --host=<hostname>
 
 psql -U <username> -d <database> -h <hostname>
 ```
+
+##### info
+```sql
+\conninfo
+```
+
+##### disconnect
+```sql
+\q
+\!
+```
 <br/><br/><br/>
 
 

diff --git a/postgres cheatsheet.md b/postgres cheatsheet.md
@@ -3,7 +3,17 @@
 This is a collection of the most common commands I run while administering Postgres databases.
 
 ##### installation
+@see http://www.postgresql.org/download/linux/ubuntu/
 @see https://help.ubuntu.com/community/PostgreSQL
+``` shell
+sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > /etc/apt/sources.list.d/pgdg.list
+wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
+sudo apt-get update
+sudo apt-get install -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5
+
+sudo su - postgres
+psql
+```
 <br/><br/><br/>
 
 
@@ -17,11 +27,6 @@ psql --username=<username> --dbname=<database> --host=<hostname>
 
 psql -U <username> -d <database> -h <hostname>
 ```
-
-##### run local script
-```sql
-psql -U <username> -d <database> -h <host> -f <local_file>
-```
 <br/><br/><br/>
 
 
@@ -234,3 +239,28 @@ WHERE <column_name> = <value>;
 <br/><br/><br/>
 
 
+
+
+
+## Scripting
+
+##### run local script
+```sql
+psql -U <username> -d <database> -h <host> -f <local_file>
+```
+
+##### backup database data
+
+##### backup database schema
+
+##### backup database functions
+
+##### backup database views
+
+##### restore database data
+
+##### restore database schema
+
+##### restore database functions
+
+##### restore database views