First Steps
1
PSQL – Introducing PostgreSQL 14
2
PSQL – How to get PostgreSQL
3
PSQL – Connecting to the PostgreSQL server
4
PSQL – Enabling access for network/remote users
5
PSQL – Using the pgAdmin4 GUI tool
6
PSQL – Using the OmniDB GUI tool
7
PSQL – Using the psql query and scripting tool
8
PSQL – Changing your password securely
9
PSQL – Avoiding hardcoding your password
10
PSQL – Using a connection service file
11
PSQL – Troubleshooting a failed connection
12
PSQL – PostgreSQL in the cloud
Exploring the Database
1
PSQL – What type of server is this?
2
PSQL – What version is the server?
3
PSQL – What is the server uptime?
4
PSQL – Locating the database server files
5
PSQL – Locating the database server’s message log
6
PSQL – Locating the database’s system identifier
7
PSQL – Listing databases on the database server
8
PSQL – How many tables are there in a database?
9
PSQL – How much disk space does a database use?
10
PSQL – How much disk space does a table use?
11
PSQL – Which are my biggest tables?
12
PSQL – How many rows are there in a table?
13
PSQL – Quickly estimating the number of rows in a table
14
PSQL – Listing extensions in this database
15
PSQL – Understanding object dependencies
Server Configuration
1
PSQL – RTFM
2
PSQL – Planning a new database
3
PSQL – Setting configuration parameters for the database server
4
PSQL – Setting configuration parameters in your programs
5
PSQL – Finding the configuration settings for your session
6
PSQL – Finding parameters with non-default settings
7
PSQL – Setting parameters for particular groups of users
8
PSQL – A basic server configuration checklist
9
PSQL – Adding an external module to PostgreSQL
10
PSQL – Using an installed module/extension
11
PSQL – Managing installed extensions
Server Control
1
PSQL – Overview of controlling the database server
2
PSQL – Starting the database server manually
3
PSQL – Stopping the server safely and quickly
4
PSQL – Stopping the server in an emergency
5
PSQL – Reloading the server configuration files
6
PSQL – Restarting the server quickly
7
PSQL – Preventing new connections
8
PSQL – Restricting users to only one session each
9
PSQL – Pushing users off the system
10
PSQL – Deciding on a design for multitenancy
11
PSQL – Using multiple schemas
12
PSQL – Giving users their own private databases
13
PSQL – Running multiple servers on one system
14
PSQL – Setting up a connection pool
15
PSQL – Accessing multiple servers using the same host and port
Tables and Data
1
PSQL – Choosing good names for database objects
2
PSQL – Handling objects with quoted names
3
PSQL – Enforcing the same name and definition for columns
4
PSQL – Identifying and removing duplicates
5
PSQL – Preventing duplicate rows
6
PSQL – Finding a unique key for a set of data
7
PSQL – Generating test data
8
PSQL – Randomly sampling data
9
PSQL – Loading data from a spreadsheet
10
PSQL – Loading data from flat files
11
PSQL – Making bulk data changes using server-side procedures with transactions
Security
1
PSQL – Overview of PostgreSQL security
2
PSQL – The PostgreSQL superuser
3
PSQL – Revoking user access to a table
4
PSQL – Granting user access to a table
5
PSQL – Granting user access to specific columns
6
PSQL – Granting user access to specific rows
7
PSQL – Creating a new user
8
PSQL – Temporarily preventing a user from connecting
9
PSQL – Removing a user without dropping their data
10
PSQL – Checking whether all users have a secure password
11
PSQL – Giving limited superuser powers to specific users
12
PSQL – Auditing database access
13
PSQL – Always knowing which user is logged in
14
PSQL – Integrating with LDAP
15
PSQL – Connecting using encryption (SSL/GSSAPI)
16
PSQL – Using SSL certificates to authenticate
17
PSQL – Mapping external usernames to database roles
18
PSQL – Using column-level encryption
19
PSQL – Setting up cloud security using predefined roles
Database Administration
1
PSQL – Writing a script that either succeeds entirely or fails entirely
2
PSQL – Writing a psql script that exits on the first error
3
PSQL – Using psql variables
4
PSQL – Placing query output into psql variables
5
PSQL – Writing a conditional psql script
6
PSQL- Investigating a psql error
7
PSQL – Setting the psql prompt with useful information
8
PSQL – Using pgAdmin for DBA tasks
9
PSQL – Scheduling jobs for regular background execution
10
PSQL – Performing actions on many tables
11
PSQL – Adding/removing columns on a table
12
PSQL – Changing the data type of a column
13
PSQL – Changing the definition of an enum data type
14
PSQL – Adding a constraint concurrently
15
PSQL – Adding/removing schemas
16
PSQL – Moving objects between schemas
17
PSQL – Adding/removing tablespaces
18
PSQL – Moving objects between tablespaces
19
PSQL – Accessing objects in other PostgreSQL databases
20
PSQL – Accessing objects in other foreign databases
21
PSQL – Making views updatable
22
PSQL- Using GENERATED data columns
23
PSQL – Using data compression
Monitoring and Diagnosis
1
PSQL – Overview of PostgreSQL monitoring
2
PSQL – Cloud-native monitoring
3
PSQL – Providing PostgreSQL information to monitoring tools
4
PSQL – Real-time viewing using pgAdmin
5
PSQL – Checking whether a user is connected
6
PSQL- Checking whether a computer is connected
7
PSQL – Repeatedly executing a query in psql
8
PSQL – Checking which queries are running
9
PSQL – Monitoring the progress of commands
10
PSQL – Checking which queries are active or blocked
11
PSQL – Knowing who is blocking a query
12
PSQL – Killing a specific session
13
PSQL – Detecting an in-doubt prepared transaction
14
PSQL – Knowing whether anybody is using a specific table
15
PSQL – Knowing when a table was last used
16
PSQL – Usage of disk space by temporary data
17
PSQL – Understanding why queries slow down
18
PSQL – Analyzing the real-time performance of your queries
19
PSQL – Investigating and reporting a bug
Regular Maintenance
1
PSQL – Controlling automatic database maintenance
2
PSQL – Avoiding auto-freezing and page corruptions
3
PSQL – Removing issues that cause bloat
4
PSQL – Removing old prepared transactions
5
PSQL – Actions for heavy users of temporary tables
6
PSQL – Identifying and fixing bloated tables and indexes
7
PSQL – Monitoring and tuning a vacuum
8
PSQL – Maintaining indexes
9
PSQL – Finding unused indexes
10
PSQL – Carefully removing unwanted indexes
11
PSQL – Planning maintenance
Performance and Concurrency
1
PSQL – Finding slow SQL statements
2
PSQL – Finding out what makes SQL slow
3
PSQL – Reducing the number of rows returned
4
PSQL – Simplifying complex SQL queries
5
PSQL – Speeding up queries without rewriting them
6
PSQL – Discovering why a query is not using an index
7
PSQL – Forcing a query to use an index
8
PSQL – Using parallel query
9
PSQL – Creating time-series tables using partitioning
10
PSQL – Using optimistic locking to avoid long lock waits
11
PSQL – Reporting performance problems
Backup and Recovery
1
PSQL – Understanding and controlling crash recovery
2
PSQL – Planning your backups
3
PSQL – Hot logical backups of one database
4
PSQL – Hot logical backups of all databases
5
PSQL – Backups of database object definitions
6
PSQL – A standalone hot physical backup
7
PSQL – Hot physical backups with Barman
8
PSQL – Recovery of all databases
9
PSQL – Recovery to a point in time
10
PSQL – Recovery of a dropped/damaged table
11
PSQL – Recovery of a dropped/damaged database
12
PSQL – Extracting a logical backup from a physical one
13
PSQL – Improving performance of logical backup/recovery
14
PSQL – Improving performance of physical backup/recovery
15
PSQL – Validating backups
Replication and Upgrades
1
PSQL – Replication concepts
2
PSQL – Replication best practices
3
PSQL – Setting up streaming replication
4
PSQL – Setting up streaming replication security
5
PSQL – Hot Standby and read scalability
6
PSQL – Managing streaming replication
7
PSQL – Using repmgr
8
PSQL – Using replication slots
9
PSQL – Monitoring replication
10
PSQL – Performance and sync rep
11
PSQL – Delaying, pausing, and synchronizing replication
12
PSQL – Logical replication
13
PSQL – BDR
14
PSQL – Archiving transaction log data
15
PSQL – Upgrading minor releases
16
PSQL – Major upgrades in-place
17
PSQL – Major upgrades online
Productivity Hacks to Get More Done in 2018
— 28 February 2017
- Facebook News Feed Eradicator (free chrome extension) Stay focused by removing your Facebook newsfeed and replacing it with an inspirational quote. Disable the tool anytime you want to see what friends are up to!
- Hide My Inbox (free chrome extension for Gmail) Stay focused by hiding your inbox. Click "show your inbox" at a scheduled time and batch processs everything one go.
- Habitica (free mobile + web app) Gamify your to do list. Treat your life like a game and earn gold goins for getting stuff done!