PostgreSQL 9 Administration Cookbook

Cover
......Page 1
Copyright......Page 3
Credits......Page 4
About the Authors......Page 5
About the Reviewers......Page 6
Table of Contents......Page 8
Preface......Page 14
Introduction......Page 20
Introducing PostgreSQL 9......Page 21
Getting PostgreSQL......Page 23
Connecting to PostgreSQL server......Page 24
Enabling access for network/remote users......Page 27
Using graphical administration tools......Page 28
Using psql query and scripting tool......Page 33
Changing your password securely......Page 37
Avoiding hardcoding your password......Page 38
Using a connection service file......Page 39
Troubleshooting a failed connection......Page 40
Introduction......Page 44
What version is the server?......Page 45
What is the server uptime?......Page 46
Locate the database server files......Page 47
Locate the database server message log......Page 49
List databases on this database server?......Page 53
How many tables in a database?......Page 56
How much disk space does a database use?......Page 58
How much disk space does a table use?......Page 59
Which are my biggest tables?......Page 60
How many rows in a table?......Page 61
in a table......Page 62
Understanding object dependencies......Page 66
Introduction......Page 70
Reading the Fine Manual (RTFM)......Page 71
Planning a new database......Page 72
Changing parameters in your programs......Page 73
What are the current configuration settings?......Page 75
settings?......Page 76
Updating the parameter file......Page 78
of users......Page 79
Basic server configuration checklist......Page 80
Adding an external module to PostgreSQL......Page 81
Running server in power saving mode......Page 83
Introduction......Page 86
Starting the database server manually......Page 87
Stopping the server safely and quickly......Page 88
Reloading the server configuration files......Page 89
Restarting the server quickly......Page 91
Preventing new connections......Page 93
Restricting users to just one session each......Page 94
Pushing users off the system......Page 96
Deciding on a design for multi-tenancy......Page 97
Using multiple schemas......Page 98
Giving users their own private database......Page 101
Running multiple servers on one system......Page 102
Set up a Connection Pool......Page 104
Introduction......Page 108
Choosing good names for database objects......Page 109
Handling objects with quoted names......Page 110
definition......Page 112
Identifying and removing duplicates......Page 116
Preventing duplicate rows......Page 119
Finding a unique key for a set of data......Page 125
Generating test data......Page 127
Randomly sampling data......Page 130
Loading data from a spreadsheet......Page 132
Loading data from flat files......Page 135
Introduction......Page 138
Revoking user access to a table......Page 139
Granting user access to a table......Page 141
Creating a new user......Page 143
connecting......Page 144
Removing a user without dropping their data......Page 146
Checking all users have a secure password......Page 147
users......Page 149
Auditing DDL changes......Page 152
Auditing data changes......Page 153
Integrating with LDAP......Page 157
Connecting using SSL......Page 158
Encrypting sensitive data......Page 160
Introduction......Page 166
or all fails......Page 167
Writing a psql script that exits on first error......Page 169
Performing actions on many tables......Page 171
Adding/Removing the columns of a table......Page 176
Changing datatype of a column......Page 178
Adding/Removing schemas......Page 181
Moving objects between schemas......Page 183
Adding/Removing tablespaces......Page 184
Moving objects between tablespaces......Page 187
databases......Page 190
Making views updateable......Page 195
Introduction......Page 202
Is the user connected?......Page 206
What are they running?......Page 207
Are they active or blocked?......Page 209
Who is blocking them?......Page 211
Killing a specific session......Page 212
Is anybody using a specific table?......Page 214
When did anybody last use it?......Page 216
data?......Page 218
Why are my queries slowing down?......Page 221
Investigating and reporting a bug......Page 223
Producing a daily summary of logfile errors......Page 225
Introduction......Page 228
maintenance......Page 229
Avoiding auto freezing and page corruptions......Page 234
Avoiding transaction wraparound......Page 235
Removing old prepared transactions......Page 238
Actions for heavy users of temporary tables......Page 240
and indexes......Page 242
Maintaining indexes......Page 246
Finding the unused indexes......Page 249
Carefully removing unwanted indexes......Page 251
Planning maintenance......Page 252
Introduction......Page 254
Finding slow SQL statements......Page 255
views......Page 258
Finding what makes SQL slow......Page 259
Reducing the number of rows returned......Page 263
Simplifying complex SQL......Page 264
Speeding up queries without rewriting them......Page 270
Why is my query not using an index?......Page 273
How do I force a query to use an index......Page 274
Using optimistic locking......Page 276
Reporting performance problems......Page 278
Introduction......Page 280
recovery......Page 281
Planning backups......Page 283
Hot logical backup of one database......Page 285
Hot logical backup of all databases......Page 287
in a tablespace......Page 289
Backup of database object definitions......Page 290
Standalone hot physical database backup......Page 291
Hot physical backup & Continuous Archiving......Page 293
Recovery of all databases......Page 296
Recovery to a point in time......Page 299
Recovery of a dropped/damaged table......Page 301
Recovery of a dropped/damaged tablespace......Page 304
Recovery of a dropped/damaged database......Page 305
Improving performance of backup/restore......Page 307
Incremental/Differential backup and restore......Page 309
Introduction......Page 312
Understanding replication concepts......Page 313
Replication best practices......Page 317
File-based log-shipping replication......Page 318
Setting up streaming log replication......Page 321
Managing log shipping replication......Page 326
Managing Hot Standby......Page 329
Selective replication using Londiste......Page 334
Selective replication using Slony 2.0......Page 338
Load balancing with pgpool-II 3.0......Page 342
Upgrading (minor)......Page 345
Major upgrades in-place......Page 346
tools......Page 348
Index......Page 350