PostgreSQL – Does psql \dt Cache Schema?

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

This is easy to fix. Edit pg_hba.conf to add a line like this:

host all postgres 127.0.0.1/32 trust

(You may need to restart Postgres server after editing it).

Then, use this:

psql -d postgres -U postgres

You should be able to get admin access without password now (that's why you added trust rule to pg_hba.conf).

Once this command works, change your password:

ALTER ROLE postgres PASSWORD 'newpassword';

After doing this, edit pg_hba.conf to remove line you added.