Search icon CANCEL
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
PostgreSQL Administration Cookbook, 9.5/9.6 Edition

You're reading from   PostgreSQL Administration Cookbook, 9.5/9.6 Edition Effective database management for administrators

Arrow left icon
Product type Paperback
Published in Apr 2017
Publisher
ISBN-13 9781785883187
Length 556 pages
Edition 3rd Edition
Languages
Arrow right icon
Authors (4):
Arrow left icon
Gianni Ciolli Gianni Ciolli
Author Profile Icon Gianni Ciolli
Gianni Ciolli
Hannu Krosing Hannu Krosing
Author Profile Icon Hannu Krosing
Hannu Krosing
Gabriele Bartolini Gabriele Bartolini
Author Profile Icon Gabriele Bartolini
Gabriele Bartolini
Simon Riggs Simon Riggs
Author Profile Icon Simon Riggs
Simon Riggs
Arrow right icon
View More author details
Toc

Table of Contents (13) Chapters Close

Preface 1. First Steps FREE CHAPTER 2. Exploring the Database 3. Configuration 4. Server Control 5. Tables and Data 6. Security 7. Database Administration 8. Monitoring and Diagnosis 9. Regular Maintenance 10. Performance and Concurrency 11. Backup and Recovery 12. Replication and Upgrades

Using a connection service file

As the number of connection options grows, you may want to consider using a connection service file.

The connection service file allows you to give a single name to a set of connection parameters. This can be accessed centrally to avoid the need for individual users to know the host and port of the database, and it is more resistant to future change.

You can set up a system-wide file as well as individual per-user files. The default file paths for these files are /etc/pg_service.conf and ~/.pg_service.conf respectively.

A system-wide connection file controls service names for all users from a single place, while a per-user file applies only to that particular user. Keep in mind that the per-user file overrides the system-wide file: if a service is defined in both the files, then the definition in the per-user file will prevail.

How to do it...

First, create a file named pg_service.conf with the following content:

[dbservice1] 
host=postgres1
port=5432
dbname=postgres

You can then copy it either to /etc/pg_service.conf or another agreed central location. You can then set the PGSYSCONFDIR environment variable to that directory location.

Alternatively, you can copy it to ~/.pg_service.conf. If you want to use a different name, set PGSERVICEFILE. Either way, you can then specify a connection string like the following:

service=dbservice1 user=sriggs

The service can also be set using an environment variable named PGSERVICE.

How it works...

This feature applies to libpq connections only, so it does not apply to JDBC.

The connection service file can also be used to specify the user, though that would mean that the username would be shared.

The pg_service.conf and .pgpass files can work together, or you can use just one of the two as you choose. Note that the pg_service.conf file is shared, so it is not a suitable place for passwords. The per-user connection service file is not shared, but in any case,
it seems best to keep things separate and confine passwords to .pgpass.

You have been reading a chapter from
PostgreSQL Administration Cookbook, 9.5/9.6 Edition - Third Edition
Published in: Apr 2017
Publisher:
ISBN-13: 9781785883187
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at €18.99/month. Cancel anytime