Postgresql unique history

postgresql

I am running several commands, some repeatedly, from the postgresql command line tool. In Bash, the history function keeps one command for each set of sequential identical commands, it seems that postgresql command line does not do that by default. So for example if I run command 'a' once, then command 'b' 100 times, I have to scroll up 100 times before running command 'a' again. This is a user-unfriendly way of going about things. Is there an option to have alternate, bash-like functionality from the postgresql command line, in which only unique commands are remembered in the history?

Best Answer

psql has a variable called HISTCONTROL. Quoting the manual:

HISTCONTROL

If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If unset, or if set to any other value than those above, all lines read in interactive mode are saved on the history list.

I have this configured to automatically take effect on psql startup by adding the line

\set HISTCONTROL ignoredups

to my .psqlrc.

Related Solutions

Postgresql – Dropping unique constraint on PostgreSQL table taking 10+ hours

Step one - go into task manager, have a look and see if anything is actually happening. If you can't see a lot of cpu and/or disk bandwidth then it's not doing anything.

Step two - check what PostgreSQL says is happening. There are two system views useful here: pg_stat_activity and pg_locks. See what's in both. The database/relation numbers in pg_locks are internal OIDs - try SELECT OID,* FROM pg_class to see which tables/indexes are being locked.

My guess is that the system is idle and it's just waiting for an exclusive lock on the table in question and something else is blocking it. The two system views should show you what is happening though.

Postgresql – Correct pg_hba.conf to allow script to run locally with a specific db-user

Since you want the backup user to connect locally from the database server. I assume the backup is running on a dedicated backup account on the OS.

Using pg_hba.conf you can specify like:

# TYPE  DATABASE USER   ADDRESS METHOD
local   all      backup_user    peer map=local_peers_backup_user

(method peer (with local/peer an address is not needed))

combine this with a pg_ident.conf like:

# MAPNAME                SYSTEM-USERNAME   PG-USERNAME
local_peers_backup_user  postgres          backup_user
local_peers_backup_user  OSBKPUSER         backup_user

This allows the os user postgres and OSBKUSER to connect to all databases in the cluster as the postgres database user named backup_user, using peer authentication. This works using sockets, on linux.

connected as your OSBKPUSER running psql suffices to get connected as backup_user, assuming that you already prepared the environment variables for the OSBKPUSER and OSBKPUSER has only 1 map. Otherwise run psql -U backup_user.

This connect method is secure, if you trust that the access to your dbserver is secured.

Best Answer

Related Solutions

Postgresql – Dropping unique constraint on PostgreSQL table taking 10+ hours

Postgresql – Correct pg_hba.conf to allow script to run locally with a specific db-user

Related Question