sblack4 · August 13, 2020 17:15 · Aug 13, 2020 · Nov 19, 2017 · Nov 19, 2017 · Apr 5, 2017
diff --git a/05schema-exploring.md b/05schema-exploring.md
@@ -0,0 +1,23 @@
+# Describe current schema
+
+### db info schema
+```
+SET search_path TO 'information_schema';
+```
+
+### list tables
+
+```
+-- list the tables in the schema
+SELECT table_name from information_schema.tables
+WHERE table_schema = 'myschema';
+```
+
+### describe table
+
+```
+SELECT column_name, is_nullable, data_type, character_maximum_length
+FROM information_schema.columns
+WHERE table_name = 'mytable'
+ORDER BY ordinal_position
+```
diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -27,6 +27,9 @@
 **Import Database #2**
 `psql -h localhost -U username -d dbname -f file.sql`
 
+**Import Database (from custom format, atomic)**
+`pg_restore -U username -d dbname -1 filename`
+
 **Dump Database (export)**
 `pg_dump -h localhost -U username dbname > /srv/db_name.backup`
 

diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -29,3 +29,9 @@
 
 **Dump Database (export)**
 `pg_dump -h localhost -U username dbname > /srv/db_name.backup`
+
+**Dump Database (custom compressed format)**
+`pg_dump -Fc -h localhost -U username dbname > /srv/db_name.backup`
+
+**Dump Database (one line with password)**
+`PGPASSWORD=abc123 pg_dump -h localhost -U username dbname > /srv/db_name.backup`
diff --git a/02guides.md b/02guides.md
@@ -1,10 +1,10 @@
 
 # Database Export and Import
 
-1. `pg_dump -U db_user -h localhost db_name -w > "/srv/dbbackups/db_name-$(date +'%y-%m-%d-%H%M').backup"`
+1. `pg_dump -h localhost -U username dbname -w > "/backups/dbname-$(date +'%y-%m-%d-%H%M').backup"`
 2. `su - postgres`
 3. `psql --set ON_ERROR_STOP=on --single-transaction db_name < backup-file`
 4. grant all permissions to the db for the db user
-   - `GRANT ALL ON ALL TABLES IN SCHEMA public to user;      `
-   - `GRANT ALL ON ALL SEQUENCES IN SCHEMA public to user;   `
-   - `GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to user;   `
+   - `GRANT ALL ON ALL TABLES IN SCHEMA public to username;      `
+   - `GRANT ALL ON ALL SEQUENCES IN SCHEMA public to username;   `
+   - `GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to username;   `
diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -7,25 +7,25 @@
 `su - postgres`
 
 **Create database user**
-`createuser -P yournamehere`
+`createuser -P username`
 
 **Delete database user**
-`dropuser`
+`dropuser username`
 
 **Create database**
-`createdb db_name`
+`createdb dbname`
 
 **Drop database**
-`dropdb db_name`
+`dropdb dbname`
 
 **Execute PSQL Command**
 `psql -c <somecommand>`
 
 **Import Database**
-`psql -U postgres -h localhost db_name < /srv/db_name.backup`
+`psql  -h localhost -U username db_name < /srv/db_name.backup`
 
 **Import Database #2**
-`psql -h hostname -d databasename -U username -f file.sql`
+`psql -h localhost -U username -d dbname -f file.sql`
 
 **Dump Database (export)**
-`pg_dump -U postgres -h localhost db_name > /srv/db_name.backup`
+`pg_dump -h localhost -U username dbname > /srv/db_name.backup`
diff --git a/04queries.md b/04queries.md
@@ -13,7 +13,7 @@
 `\du` or `\dg`
 
 **Select database**
-`\c db` or `\connect db`
+`\c db` or `\connect dbname`
 
 **See currently selected database**
 `\c` or `\connect`
@@ -25,19 +25,19 @@
 `\q` or `CTRL+D`
 
 **Create user**
-`CREATE USER username WITH password 'tmppassword';`
+`CREATE USER username WITH PASSWORD 'password';`
 
 **Drop User**
-`DROP USER username`
+`DROP USER username;`
 
 **Create Database with owner**
-`CREATE DATABASE mydb WITH OWNER ramesh;`
+`CREATE DATABASE dbname WITH OWNER username;`
 
 **Change user password**
-`ALTER USER postgres PASSWORD 'newPassword';`
+`ALTER USER postgres PASSWORD 'new_password';`
 
 **Give privileges to database to user**
-`GRANT ALL PRIVILEGES ON DATABASE db TO user;`
+`GRANT ALL PRIVILEGES ON DATABASE dbname TO username;`
 
 **Drop Database**
-`DROP DATABASE db;`
+`DROP DATABASE dbname;`
diff --git a/04queries.md b/04queries.md
@@ -25,7 +25,10 @@
 `\q` or `CTRL+D`
 
 **Create user**
-`CREATE USER ramesh WITH password 'tmppassword';`
+`CREATE USER username WITH password 'tmppassword';`
+
+**Drop User**
+`DROP USER username`
 
 **Create Database with owner**
 `CREATE DATABASE mydb WITH OWNER ramesh;`

diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -24,5 +24,8 @@
 **Import Database**
 `psql -U postgres -h localhost db_name < /srv/db_name.backup`
 
+**Import Database #2**
+`psql -h hostname -d databasename -U username -f file.sql`
+
 **Dump Database (export)**
 `pg_dump -U postgres -h localhost db_name > /srv/db_name.backup`
diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -21,7 +21,7 @@
 **Execute PSQL Command**
 `psql -c <somecommand>`
 
-**Dump Database**
+**Import Database**
 `psql -U postgres -h localhost db_name < /srv/db_name.backup`
 
 **Dump Database (export)**

diff --git a/04queries.md b/04queries.md
@@ -12,6 +12,9 @@
 **List users**
 `\du` or `\dg`
 
+**Select database**
+`\c db` or `\connect db`
+
 **See currently selected database**
 `\c` or `\connect`
 

diff --git a/01PostgreSQLSnippets.md b/01PostgreSQLSnippets.md
@@ -5,4 +5,5 @@
 
 # Resources
 
-* [Hyperpolyglot Cheatsheet](http://hyperpolyglot.org/db)
+* [PostgreSQL command line cheatsheet](https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546)
+* [Hyperpolyglot Cheatsheet](http://hyperpolyglot.org/db)
diff --git a/01PostgreSQLSnippets.md b/01PostgreSQLSnippets.md
@@ -1,3 +1,8 @@
 # Notes
 
 * 'postgres' user is superuser
+
+
+# Resources
+
+* [Hyperpolyglot Cheatsheet](http://hyperpolyglot.org/db)
diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -18,6 +18,9 @@
 **Drop database**
 `dropdb db_name`
 
+**Execute PSQL Command**
+`psql -c <somecommand>`
+
 **Dump Database**
 `psql -U postgres -h localhost db_name < /srv/db_name.backup`
 

diff --git a/04queries.md b/04queries.md
@@ -1,13 +1,19 @@
 # Queries and commands inside PSQL
 
+**General Help**
+`\?`
+
+**Syntax Help on SQL Commands**
+`\help <somequery>`
+
 **List Databases**
-`\l`
+`\l` or `\list`
 
 **List users**
-`\du`
+`\du` or `\dg`
 
 **See currently selected database**
-`\c`
+`\c` or `\connect`
 
 **Import database**
 `\i <file>`

diff --git a/04queries.md b/04queries.md
@@ -1,5 +1,20 @@
 # Queries and commands inside PSQL
 
+**List Databases**
+`\l`
+
+**List users**
+`\du`
+
+**See currently selected database**
+`\c`
+
+**Import database**
+`\i <file>`
+
+**Quit PSQL**
+`\q` or `CTRL+D`
+
 **Create user**
 `CREATE USER ramesh WITH password 'tmppassword';`
 
@@ -12,14 +27,5 @@
 **Give privileges to database to user**
 `GRANT ALL PRIVILEGES ON DATABASE db TO user;`
 
-**List Databases**
-`\l`
-
-**List users**
-`\du`
-
-**Import database**
-`\i <file>`
-
-**Quit PSQL**
-`\q` or `CTRL+D`
+**Drop Database**
+`DROP DATABASE db;`
diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -1,6 +1,6 @@
 # Bash Commands
 
-**Log in to psql**
+**Login into psql**
 `sudo -u postgres psql`
 
 **Login into postgres user**

diff --git a/03psql-pg_dump-etc.md b/03psql-pg_dump-etc.md
@@ -1,25 +1,25 @@
 # Bash Commands
 
 **Log in to psql**
-- `sudo -u postgres psql`
+`sudo -u postgres psql`
 
 **Login into postgres user**
-- `su - postgres`
+`su - postgres`
 
 **Create database user**
-- `createuser -P yournamehere`
+`createuser -P yournamehere`
 
 **Delete database user**
-- `dropuser`
+`dropuser`
 
 **Create database**
-- `createdb db_name`
+`createdb db_name`
 
 **Drop database**
-- `dropdb db_name`
+`dropdb db_name`
 
 **Dump Database**
-- `psql -U postgres -h localhost db_name < /srv/db_name.backup`
+`psql -U postgres -h localhost db_name < /srv/db_name.backup`
 
 **Dump Database (export)**
-- `pg_dump -U postgres -h localhost db_name > /srv/db_name.backup`
+`pg_dump -U postgres -h localhost db_name > /srv/db_name.backup`
diff --git a/03 psql-pg_dump-etc → 03psql-pg_dump-etc.md b/03 psql-pg_dump-etc → 03psql-pg_dump-etc.md
diff --git a/03 psql-pg_dump-etc b/03 psql-pg_dump-etc
@@ -1,23 +1,25 @@
-### Log in to psql
+# Bash Commands
+
+**Log in to psql**
 - `sudo -u postgres psql`
 
-### Login into postgres user
+**Login into postgres user**
 - `su - postgres`
 
-### Create database user
+**Create database user**
 - `createuser -P yournamehere`
 
-### Delete database user
+**Delete database user**
 - `dropuser`
 
-### Create database
+**Create database**
 - `createdb db_name`
 
-### Drop database
+**Drop database**
 - `dropdb db_name`
 
-### Dump Database
+**Dump Database**
 - `psql -U postgres -h localhost db_name < /srv/db_name.backup`
 
-### Dump Database (export)
+**Dump Database (export)**
 - `pg_dump -U postgres -h localhost db_name > /srv/db_name.backup`
diff --git a/04queries.md b/04queries.md
@@ -1,33 +1,25 @@
 # Queries and commands inside PSQL
 
 **Create user**
-
 `CREATE USER ramesh WITH password 'tmppassword';`
 
 **Create Database with owner**
-
 `CREATE DATABASE mydb WITH OWNER ramesh;`
 
 **Change user password**
-
 `ALTER USER postgres PASSWORD 'newPassword';`
 
 **Give privileges to database to user**
-
 `GRANT ALL PRIVILEGES ON DATABASE db TO user;`
 
 **List Databases**
-
 `\l`
 
 **List users**
-
 `\du`
 
 **Import database**
-
 `\i <file>`
 
 **Quit PSQL**
-
 `\q` or `CTRL+D`
diff --git a/04queries.md b/04queries.md
@@ -1,25 +1,33 @@
 # Queries and commands inside PSQL
 
 **Create user**
+
 `CREATE USER ramesh WITH password 'tmppassword';`
 
 **Create Database with owner**
+
 `CREATE DATABASE mydb WITH OWNER ramesh;`
 
 **Change user password**
+
 `ALTER USER postgres PASSWORD 'newPassword';`
 
 **Give privileges to database to user**
+
 `GRANT ALL PRIVILEGES ON DATABASE db TO user;`
 
 **List Databases**
+
 `\l`
 
 **List users**
+
 `\du`
 
 **Import database**
+
 `\i <file>`
 
 **Quit PSQL**
+
 `\q` or `CTRL+D`
diff --git a/01 PostgreSQL Snippets b/01 PostgreSQL Snippets
@@ -1,2 +0,0 @@
-### Notes
-- 'postgres' user is superuser

diff --git a/01PostgreSQLSnippets.md b/01PostgreSQLSnippets.md
@@ -0,0 +1,3 @@
+# Notes
+
+* 'postgres' user is superuser
diff --git a/02 guides → 02guides.md b/02 guides → 02guides.md
@@ -1,4 +1,6 @@
-### Database Export and Import
+
+# Database Export and Import
+
 1. `pg_dump -U db_user -h localhost db_name -w > "/srv/dbbackups/db_name-$(date +'%y-%m-%d-%H%M').backup"`
 2. `su - postgres`
 3. `psql --set ON_ERROR_STOP=on --single-transaction db_name < backup-file`

diff --git a/04queries.md b/04queries.md
@@ -1,3 +1,5 @@
+# Queries and commands inside PSQL
+
 **Create user**
 `CREATE USER ramesh WITH password 'tmppassword';`
 

diff --git a/04queries.md b/04queries.md
@@ -1,13 +1,13 @@
-# Create user
+**Create user**
 `CREATE USER ramesh WITH password 'tmppassword';`
 
-# Create Database with owner
+**Create Database with owner**
 `CREATE DATABASE mydb WITH OWNER ramesh;`
 
-# Change user password
+**Change user password**
 `ALTER USER postgres PASSWORD 'newPassword';`
 
-# Give privileges to database to user
+**Give privileges to database to user**
 `GRANT ALL PRIVILEGES ON DATABASE db TO user;`
 
 **List Databases**

diff --git a/queries.md → 04queries.md b/queries.md → 04queries.md
@@ -1,23 +1,23 @@
-### Create user
+# Create user
 `CREATE USER ramesh WITH password 'tmppassword';`
 
-### Create Database with owner
+# Create Database with owner
 `CREATE DATABASE mydb WITH OWNER ramesh;`
 
-### Change user password
+# Change user password
 `ALTER USER postgres PASSWORD 'newPassword';`
 
-### Give privileges to database to user
+# Give privileges to database to user
 `GRANT ALL PRIVILEGES ON DATABASE db TO user;`
 
-### List Databases
+**List Databases**
 `\l`
 
-### List users
+**List users**
 `\du`
 
-### Import database
+**Import database**
 `\i <file>`
 
-### Quit PSQL
-`\q` or CTRL+D
+**Quit PSQL**
+`\q` or `CTRL+D`
diff --git a/04 queries → queries.md b/04 queries → queries.md
diff --git a/04 queries b/04 queries
@@ -1,20 +1,23 @@
 ### Create user
-- `CREATE USER ramesh WITH password 'tmppassword';`
+`CREATE USER ramesh WITH password 'tmppassword';`
 
 ### Create Database with owner
-- `CREATE DATABASE mydb WITH OWNER ramesh;`
+`CREATE DATABASE mydb WITH OWNER ramesh;`
 
 ### Change user password
-- `ALTER USER postgres PASSWORD 'newPassword';`
+`ALTER USER postgres PASSWORD 'newPassword';`
 
 ### Give privileges to database to user
-- `GRANT ALL PRIVILEGES ON DATABASE db TO user;`
+`GRANT ALL PRIVILEGES ON DATABASE db TO user;`
 
 ### List Databases
-- `\l`
+`\l`
 
 ### List users
-- `\du`
+`\du`
 
 ### Import database
-- `\i <file>`
+`\i <file>`
+
+### Quit PSQL
+`\q` or CTRL+D
diff --git a/03 psql-pg_dump-etc b/03 psql-pg_dump-etc
@@ -1,6 +1,21 @@
 ### Log in to psql
 - `sudo -u postgres psql`
 
+### Login into postgres user
+- `su - postgres`
+
+### Create database user
+- `createuser -P yournamehere`
+
+### Delete database user
+- `dropuser`
+
+### Create database
+- `createdb db_name`
+
+### Drop database
+- `dropdb db_name`
+
 ### Dump Database
 - `psql -U postgres -h localhost db_name < /srv/db_name.backup`
 

diff --git a/04 queries b/04 queries
@@ -18,19 +18,3 @@
 
 ### Import database
 - `\i <file>`
-
-### Login into postgres user
-- `su - postgres`
-
-### Create database user
-- `createuser -P yournamehere`
-
-### Delete database user
-- `dropuser`
-
-### Create database
-- `createdb db_name`
-
-### Drop database
-- `dropdb db_name`
-
diff --git a/03 psql-pg_dump-etc b/03 psql-pg_dump-etc
@@ -1,3 +1,6 @@
+### Log in to psql
+- `sudo -u postgres psql`
+
 ### Dump Database
 - `psql -U postgres -h localhost db_name < /srv/db_name.backup`
 

diff --git a/01 notes b/01 notes
@@ -1 +0,0 @@
-- 'postgres' user is superuser

diff --git a/01 PostgreSQL Snippets b/01 PostgreSQL Snippets
@@ -0,0 +1,2 @@
+### Notes
+- 'postgres' user is superuser
diff --git a/notes → 01 notes b/notes → 01 notes
diff --git a/guides → 02 guides b/guides → 02 guides
diff --git a/psql-pg_dump-etc → 03 psql-pg_dump-etc b/psql-pg_dump-etc → 03 psql-pg_dump-etc
diff --git a/queries → 04 queries b/queries → 04 queries
diff --git a/notes b/notes
@@ -0,0 +1 @@
+- 'postgres' user is superuser
diff --git a/psql-pg_dump-etc b/psql-pg_dump-etc
@@ -1,9 +1,5 @@
-### Notes
-- 'postgres' user is superuser
-
-
 ### Dump Database
-- `psql -U postgres -h localhost enboard_dev < /srv/enboard_dev_db.backup`
+- `psql -U postgres -h localhost db_name < /srv/db_name.backup`
 
 ### Dump Database (export)
-- `pg_dump -U postgres -h localhost enboard_dev > /srv/enboard_dev_db.backup`
+- `pg_dump -U postgres -h localhost db_name > /srv/db_name.backup`
No results found