book

PostgreSQL: Up and Running, 3rd Edition

by Regina O. Obe, Leo S. Hsu

October 2017

Intermediate to advanced

314 pages

8h 13m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

AudienceFor More Information on PostgreSQLCode and Output FormattingConventions Used in This BookUsing Code ExamplesO’Reilly SafariHow to Contact Us
Why PostgreSQL?Why Not PostgreSQL?Where to Get PostgreSQLAdministration ToolspsqlpgAdminphpPgAdminAdminerPostgreSQL Database ObjectsWhat’s New in Latest Versions of PostgreSQL?Why Upgrade?Features Introduced in PostgreSQL 10Features Introduced in PostgreSQL 9.6Features Introduced in PostgreSQL 9.5Features Introduced in PostgreSQL 9.4Database DriversWhere to Get HelpNotable PostgreSQL Forks
Configuration FilesMaking Configurations Take EffectThe postgresql.conf FileThe pg_hba.conf FileManaging ConnectionsCheck for Queries Being BlockedRolesCreating Login RolesCreating Group RolesDatabase CreationTemplate DatabasesUsing SchemasPrivilegesTypes of PrivilegesGetting StartedGRANTDefault PrivilegesPrivilege IdiosyncrasiesExtensionsInstalling ExtensionsCommon ExtensionsBackup and RestoreSelective Backup Using pg_dumpSystemwide Backup Using pg_dumpallRestoring DataManaging Disk Storage with TablespacesCreating TablespacesMoving Objects Among TablespacesVerboten PracticesDon’t Delete PostgreSQL Core System Files and BinariesDon’t Grant Full OS Administrative Privileges to the Postgres System Account (postgres)Don’t Set shared_buffers Too HighDon’t Try to Start PostgreSQL on a Port Already in Use
Environment VariablesInteractive versus Noninteractive psqlpsql CustomizationsCustom PromptsTiming ExecutionsAutocommit CommandsShortcutsRetrieving Prior Commandspsql GemsExecuting Shell CommandsWatching StatementsRetrieving Details of Database ObjectsCrosstabsDynamic SQL ExecutionImporting and Exporting Datapsql Importpsql ExportCopying from or to ProgramBasic Reporting
Getting StartedOverview of FeaturesConnecting to a PostgreSQL ServerNavigating pgAdminpgAdmin FeaturesAutogenerating Queries from Table DefinitionsAccessing psql from pgAdmin3Editing postgresql.conf and pg_hba.conf from pgAdmin3Creating Database Assets and Setting PrivilegesImport and ExportBackup and RestorepgScriptGraphical ExplainJob Scheduling with pgAgentInstalling pgAgentScheduling JobsHelpful pgAgent Queries
NumericsSerialsGenerate Series FunctionTextualsString FunctionsSplitting Strings into Arrays, Tables, or SubstringsRegular Expressions and Pattern MatchingTemporalsTime Zones: What They Are and Are NotDatetime Operators and FunctionsArraysArray ConstructorsUnnesting Arrays to RowsArray Slicing and SplicingReferencing Elements in an ArrayArray Containment ChecksRange TypesDiscrete Versus Continuous RangesBuilt-in Range TypesDefining RangesDefining Tables with RangesRange OperatorsJSONInserting JSON DataQuerying JSONOutputting JSONBinary JSON: jsonbEditing JSONB dataXMLInserting XML DataQuerying XML DataFull Text SearchFTS ConfigurationsTSVectorsTSQueriesUsing Full Text SearchRanking ResultsFull Text StrippingFull Text Support for JSON and JSONBCustom and Composite Data TypesAll Tables Are Custom Data TypesBuilding Custom Data TypesComposites and NULLsBuilding Operators and Functions for Custom Types
TablesBasic Table CreationInherited TablesPartitioned TablesUnlogged TablesTYPE OFConstraintsForeign Key ConstraintsUnique ConstraintsCheck ConstraintsExclusion ConstraintsIndexesPostgreSQL Stock IndexesOperator ClassesFunctional IndexesPartial IndexesMulticolumn Indexes
ViewsSingle Table ViewsUsing Triggers to Update ViewsMaterialized ViewsHandy ConstructionsDISTINCT ONLIMIT and OFFSETShorthand CastingMultirow InsertILIKE for Case-Insensitive SearchANY Array SearchSet-Returning Functions in SELECTRestricting DELETE, UPDATE, and SELECT from Inherited TablesDELETE USINGReturning Affected Records to the UserUPSERTs: INSERT ON CONFLICT UPDATEComposite Types in QueriesDollar QuotingDOFILTER Clause for AggregatesPercentiles and ModeWindow FunctionsPARTITION BYORDER BYCommon Table ExpressionsBasic CTEsWritable CTEsRecursive CTELateral JoinsWITH ORDINALITYGROUPING SETS, CUBE, ROLLUP
Anatomy of PostgreSQL FunctionsFunction BasicsTriggers and Trigger FunctionsAggregatesTrusted and Untrusted LanguagesWriting Functions with SQLBasic SQL FunctionWriting SQL Aggregate FunctionsWriting PL/pgSQL FunctionsBasic PL/pgSQL FunctionWriting Trigger Functions in PL/pgSQLWriting PL/Python FunctionsBasic Python FunctionWriting PL/V8, PL/CoffeeScript, and PL/LiveScript FunctionsBasic FunctionsWriting Aggregate Functions with PL/V8Writing Window Functions in PL/V8
EXPLAINEXPLAIN OptionsSample Runs and OutputGraphical OutputsGathering Statistics on StatementsWriting Better QueriesOverusing Subqueries in SELECTAvoid SELECT *Make Good Use of CASEUsing FILTER Instead of CASEParallelized QueriesWhat Does a Parallel Query Plan Look Like?Parallel ScansParallel JoinsGuiding the Query PlannerStrategy SettingsHow Useful Is Your Index?Table StatisticsRandom Page Cost and Quality of DrivesCaching

Replication OverviewReplication JargonEvolution of PostgreSQL ReplicationThird-Party Replication OptionsSetting Up Full Server ReplicationConfiguring the MasterConfiguring the Slaves for Full Server Cluster ReplicationInitiating the Streaming Replication ProcessReplicating Only Some Tables or Databases with Logical ReplicationForeign Data WrappersQuerying Flat FilesQuerying Flat Files as Jagged ArraysQuerying Other PostgreSQL ServersQuerying Other Tabular Formats with ogr_fdwQuerying Nonconventional Data Sources
Windows and Desktop LinuxCentOS, Fedora, Red Hat, Scientific LinuxDebian, UbuntuFreeBSDmacOS
Database Backup Using pg_dumpServer Backup: pg_dumpallDatabase Restore: pg_restorepsql Interactive Commandspsql Noninteractive Commands

Content preview from PostgreSQL: Up and Running, 3rd Edition

Chapter 2. Database Administration

This chapter covers what we consider basic administration of a PostgreSQL server: managing roles and permissions, creating databases, installing extensions, and backing up and restoring data. Before continuing, you should have already installed PostgreSQL and have administration tools at your disposal.

Configuration Files

Three main configuration files control operations of a PostgreSQL server:

postgresql.conf: Controls general settings, such as memory allocation, default storage location for new databases, the IP addresses that PostgreSQL listens on, location of logs, and plenty more.
pg_hba.conf: Controls access to the server, dictating which users can log in to which databases, which IP addresses can connect, and which authentication scheme to accept.
pg_ident.conf: If present, this file maps an authenticated OS login to a PostgreSQL user. People sometimes map the OS root account to the PostgresSQL superuser account, postgres.

Note

PostgreSQL officially refers to users as roles. Not all roles need to have login privileges. For example, group roles often do not. We use the term user to refer to a role with login privileges.

If you accepted default installation options, you will find these configuration files in the main PostgreSQL data folder. You can edit them using any text editor or the Admin Pack in pgAdmin. Instructions for editing with pgAdmin are in “Editing postgresql.conf and pg_hba.conf from pgAdmin3”. If you are unable to find the physical location ...