Postgres 10 System-Wide Startup File on Ubuntu 18.04

postgresqlpsqlUbuntu

When I log into postgres under my personal shell account, my .psqlrc file runs a series of meta commands to set up what is for me an optimum working environment. I'd like to set that same environment as the default when I or anyone else logs in as user postgres. I've been searching for a long time for a way to do this. The closest I have come is the following.

  1. Find the system-wide configuration directory:
pg_config --sysconfdir

This returns /etc/postgresql-common. After cd'ing into this directory, can find no psqlrc file. So I make one, adding a test line to turn off that annoying pager behavior:

\pset pager off

…and save. So far, so good. Permissions and ownership (root) all look good, same as the other files in this directory. Then I try logging into postgres as postgres:

sudo -u postgres psql postgres

and…nothing. No change.

Next, because we have more than one db cluster (currently running pg 10, but also have an inactive pre-upgrade pg 9.3 cluster that I'm not quite ready to get rid of) I get back into /etc/postgresql-common and rename psqlrc to psqlrc-10, following instructions here: http://manpages.ubuntu.com/manpages/bionic/man1/psql.1.html.

Finally, I log back into postgres as postgres and…no change. I've also tried logging out entirely and logging back in. Still no change.

This is the latest of many attempts. I'm sparing you the earlier versions. Is it possible that /etc/postgresql-common/psqlrc only affects interactive terminal behavior for non-postgres users? If so, how do I make changes for user postgres? If not, where on earth is that darn system-wide psqlrc file?

Best Answer

/etc/postgresql-common/psqlrc does work with Ubuntu packages.

Working example with Ubuntu 18.04 and PostgreSQL 11 from apt.postgresql.org.

$ echo "\echo This is the common psql rc file" | sudo tee /etc/postgresql-common/psqlrc
\echo This is the common psql rc file

$ ls -l /etc/postgresql-common/psqlrc 
-rw-r--r-- 1 root root 32 mai   23 11:36 /etc/postgresql-common/psqlrc

$ sudo -u postgres psql postgres
This is the common psql rc file
psql (11.3 (Ubuntu 11.3-1.pgdg18.04+1))
Type "help" for help.

postgres=#

When such things don't produce the expected result, a quick way to troubleshoot is to launch the command with strace and examine failures/successes to open files within the captured stderr output.

Example:

$ sudo -u postgres strace psql postgres 2>/tmp/strace-output
This is the common psql rc file
psql (11.3 (Ubuntu 11.3-1.pgdg18.04+1))
Type "help" for help.

postgres=# \q

$ grep psqlrc /tmp/strace-output 
access("/etc/postgresql-common/psqlrc-11.3 (Ubuntu 11.3-1.pgdg18.04+1)", R_OK) = -1 ENOENT (No such file or directory)
access("/etc/postgresql-common/psqlrc-11", R_OK) = -1 ENOENT (No such file or directory)
access("/etc/postgresql-common/psqlrc", R_OK) = 0
openat(AT_FDCWD, "/etc/postgresql-common/psqlrc", O_RDONLY) = 4
access("/var/lib/postgresql/.psqlrc-11.3 (Ubuntu 11.3-1.pgdg18.04+1)", R_OK) = -1 ENOENT (No such file or directory)
access("/var/lib/postgresql/.psqlrc-11", R_OK) = -1 ENOENT (No such file or directory)
access("/var/lib/postgresql/.psqlrc", R_OK) = -1 ENOENT (No such file or directory)

In your case if you have a  /etc/postgresql-common/psqlrc you will see what exact errors occur when trying to access it.

Related Question