POSTGRESQL

Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Sed molestie, velit ut eleifend sollicitudin, neque orci tempor nulla, id sagittis nisi ante nec arcu.

CHAPTER 1: FIRST STEP

  • Introduction
  • Introducing PostgreSQL 9
  • Getting PostgreSQL
  • Connecting to PostgreSQL server
  • Enabling access for network/remote users
  • Using graphical administration tools
  • Using psql query and scripting tool
  • Changing your password securely
  • Avoiding hardcoding your password
  • Using a connection service file
  • Troubleshooting a failed connection

CHAPTER 2: EXPLORING THE DATABASE

  • Introduction
  • What version is the server?
  • What is the server uptime?
  • Locate the database server files
  • Locate the database server message log
  • List databases on this database server?
  • How many tables in a database?
  • How much disk space does a database use?
  • How much disk space does a table use?
  • Which are my biggest tables?
  • How many rows in a table?
  • Quick estimate of the number of rows in a table
  • Understanding object dependencies

 

CHAPTER 3: CONFIGURATION

  • Introduction
  • Reading the Fine Manual (RTFM)
  • Planning a new database
  • Changing parameters in your programs
  • What are the current configuration settings?
  • Which parameters are at non-default settings?
  • Updating the parameter file
  • Setting parameters for particular groups of users
  • Basic server configuration checklist
  • Adding an external module to PostgreSQL
  • Running server in power saving mode

CHAPTER 4: SERVER CONTROL

  • Introduction
  • Starting the database server manually
  • Stopping the server safely and quickly
  • Stopping the server in an emergency
  • Reloading the server configuration files
  • Restarting the server quickly
  • Preventing new connections
  • Restricting users to just one session each
  • Pushing users off the system
  • Deciding on a design for multi-tenancy
  • Using multiple schemas
  • Giving users their own private database
  • Running multiple servers on one system
  • Set up a Connection Pool

CHAPTER 5: TABLES & DATA

  • Introduction
  • Choosing good names for database objects
  • Handling objects with quoted names
  • Enforcing same name, same column definition
  • Identifying and removing duplicates
  • Preventing duplicate rows
  • Finding a unique key for a set of data
  • Generating test data
  • Randomly sampling data
  • Loading data from a spreadsheet
  • Loading data from flat files

CHAPTER 6: SECURITY

  • Introduction
  • Revoking user access to a table
  • Granting user access to a table
  • Creating a new user
  • Temporarily preventing a user from connecting
  • Removing a user without dropping their data
  • Checking all users have a secure password
  • Giving limited superuser powers to specific users
  • Auditing DDL changes
  • Auditing data changes
  • Integrating with LDAP
  • Connecting using SSL
  • Encrypting sensitive data

CHAPTER 7: DATABASE ADMINISTRATION

  • Introduction
  • Writing a script that either all succeeds or all fails
  • Writing a psql script that exits on first error
  • Performing actions on many tables
  • Adding/Removing the columns of a table
  • Changing datatype of a column
  • Adding/Removing schemas
  • Moving objects between schemas
  • Adding/Removing tablespaces
  • Moving objects between tablespaces
  • Accessing objects in other PostgreSQL databases
  • Making views updateable

CHAPTER 8: MONITORING AND DIAGNOSIS

  • Introduction
  • Is the user connected?
  • What are they running?
  • Are they active or blocked?
  • Who is blocking them?
  • Killing a specific session
  • Resolving an in-doubt prepared transaction
  • Is anybody using a specific table?
  • When did anybody last use it?
  • How much disk space is used by temporary data?
  • Why are my queries slowing down?
  • Investigating and reporting a bug
  • Producing a daily summary of log file errors

CHAPTER 9: REGULAR MAINTENANCE

  • Introduction
  • Controlling automatic database maintenance
  • Avoiding auto freezing and page corruptions
  • Avoiding transaction wraparound
  • Removing old prepared transactions
  • Actions for heavy users of temporary tables
  • Identifying and fixing bloated tables and indexes
  • Maintaining indexes
  • Finding the unused indexes
  • Carefully removing unwanted indexes
  • Planning maintenance

CHAPTER 10: PERFORMANCE & CONCURRENCY

  • Introduction
  • Finding slow SQL statements
  • Collecting regular statistics from pg_stat* views
  • Finding what makes SQL slow
  • Reducing the number of rows returned
  • Simplifying complex SQL
  • Speeding up queries without rewriting them
  • Why is my query not using an index?
  • How do I force a query to use an index
  • Using optimistic locking
  • Reporting performance problems

CHAPTER 11: BACKUP & RECOVERY

  • Introduction
  • Understanding and controlling crash recovery
  • Planning backups
  • Hot logical backup of one database
  • Hot logical backup of all databases
  • Hot logical backup of all tables in a tablespace
  • Backup of database object definitions
  • Standalone hot physical database backup
  • Hot physical backup & Continuous Archiving
  • Recovery of all databases
  • Recovery to a point in time
  • Recovery of a dropped/damaged table
  • Recovery of a dropped/damaged tablespace
  • Recovery of a dropped/damaged database
  • Improving performance of backup/restore
  • Incremental/Differential backup and restore

CHAPTER 12: REPLICATION, UPGRADES, MIGRATION & MAINTENANCE

  • Introduction
  • Understanding replication concepts
  • Replication best practices
  • File-based log-shipping replication
  • Setting up streaming log replication
  • Managing log shipping replication
  • Managing Hot Standby
  • Selective replication using Londiste
  • Selective replication using Slony 2.0
  • Load balancing with pgpool-II 3.0
  • Upgrading (minor)
  • Major upgrades in-place
  • Major upgrades online using replication tools
  • Upgrading PostgreSQL.
  • Migration from Oracle to Postgres

CONTACT US

Free Courses

Duis egestas aliquet aliquet. Maecenas erat eros, fringilla et leo eget, viverra pretium nulla. Quisque sed augue tincidunt, posuere dui tempor.

Premium Courses

Duis egestas aliquet aliquet. Maecenas erat eros, fringilla et leo eget, viverra pretium nulla. Quisque sed augue tincidunt, posuere dui tempor.