Server Fault Stack Exchange
Server Fault Stack Exchange

Questions tagged [psql]

psql is official postgresql client

psql is the official postgresql database client. It provides a command line interface (CLI), and it can be used to establish an interactive session with a postgresql database server and execute SQL queries. It can also be used to import an SQL file and execute it against one of the postgrseql DBs.

50 questions
86
votes
6 answers

Turn off pager for psql's interactive output

We switched from PostgreSQL 8.3 to 9.0. Perhaps it's a new feature or perhaps just a configuration change, but now when output from commands (like, \d tablename) exceeds visible vertical space, psql seem to pipe the output through something similar…
Yuri Ushakov
  • 983
  • 2
  • 7
  • 9
36
votes
2 answers

Postgres: Non zero exit code when executing a sql file?

I am writing a shell script which makes calls to psql using 2 forms... one is by command (-c), the other is by file (-f). e.g. psql -c "create table foo (bar integer)" psql -f foobar.sql One difference between these forms is that a call by command…
Jin Kim
  • 963
  • 2
  • 9
  • 12
26
votes
3 answers

How can I show the content of a trigger with psql?

I know I can list the triggers with \dft. But how can I see one concrete trigger? I want to know details like on which events the trigger is executed, which function is executed and so on.
Mnementh
  • 1,075
  • 2
  • 11
  • 18
24
votes
1 answer

Postgres roles best practice implementation

Folks, I could use your help making my Postgres user access control design better and more aligned with best practices. I'm helping roll out a small production Postgres server but I am not a DB admin, so I know just enough to be dangerous. There is…
JP Beaudry
  • 251
  • 2
  • 6
15
votes
2 answers

How to specify a client certificate to psql?

I have a Postgres server with a user dev which requires a client certificate to log in. I'm using the command psql "sslmode=require user=dev host=db.prod", which gives me psql: FATAL: connection requires a valid client certificate. I know where the…
Jorn
  • 441
  • 1
  • 4
  • 13
8
votes
2 answers

psql empty database before restore

I want to restore a database of postgreSQL, but before do it i need to empty the restore target database. Is there an option of psql to do it ? Now i use a command line as the following : psql -U postgres db_test < testdb.sql
aleroot
  • 3,160
  • 5
  • 28
  • 37
7
votes
1 answer

pg_dump: [archiver (db)] connection to database failed: FATAL: Peer authentication failed for user "postgres"

I'm trying to backup my Postgres DB to another server but I keep getting denied access. my pg_hba.conf file looks like this: # DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the…
Drace
  • 175
  • 1
  • 1
  • 4
6
votes
1 answer

Set connection timeout using psql command line interface

Is there a way to set the connection timeout using psql cli? How can I set a connection timeout to something low like 3 seconds using the following: PGPASSWORD=passwordhere psql -h 10.0.0.144 -U myuser -c "select 1" -d mydatabase As far as I can…
Justin
  • 5,008
  • 19
  • 58
  • 82
5
votes
1 answer

`psql` expanded mode equivalency for `mysql`

Am frustated by things like: 0:33:1407402356:root@ahost:~# echo 'use wordpress_3_6_1; select * from wp_posts;' | mysql -u mysqluser -pmysqlpasswdord | wc -l -L 42 40585 SQL query results being mangled ugly. PostgreSQL s psql provides the helper…
178024
  • 36
  • 3
  • 15
4
votes
2 answers

Which package to upgrade psql client?

I have updated my Postgres install from 8.4 to 9.3. Now when I log onto the server with the initial command sudo -u postgres psql template1 I get the following error psql (8.4.18, server 9.3.5) WARNING: psql version 8.4, server version 9.3. …
wobbily_col
  • 643
  • 2
  • 7
  • 14
4
votes
1 answer

SSH tunnel to a postgres database

Currently I develop on a remote dev server. I SSH into the dev server (using SSH keys) as ssh -p 22222 user@devbox.com Then from the dev server, I connect to the database over the local network psql -U postgres -h psqldb -d my_database where…
hobbes3
  • 545
  • 2
  • 9
  • 23
4
votes
2 answers

PSQL 64bit driver error

I have an Ubuntu 12.04 64bit server setup under Hyper-V. I have installed Pervasive 64bit SQL drivers so that a stock-updater script can run daily (Updates external MySQL database from another local server running Exchequer software / PSQL…
Alexander Holsgrove
  • 237
  • 1
  • 3
  • 15
4
votes
1 answer

psql editor setting on Ubuntu

The situation is the following. This is an Ubuntu box: Linux ns3mx3 2.6.32-41-server #89-Ubuntu SMP Fri Apr 27 22:33:31 UTC 2012 x86_64 GNU/Linux Which means that when I first issue \e in psql, I'm asked to choose an editor. Then there is the…
dezso
  • 149
  • 9
3
votes
2 answers

psql asks for password despite configuring trust authentication from localhost

In /var/lib/pgsql/9.1/data/pg_hba.conf, I have the following: # TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 trust But when I execute su -c "psql -d postgres" -…
Steve
  • 200
  • 2
  • 4
  • 13
2
votes
4 answers

Postgresql: restoring database from dump fails

I am using psql (9.3.9) on Ubuntu 14.04. I created dump of a database using command: pg_dump db1 > db1.backup I deleted the db and recreated it. Trying to restore it with psql -d db1 -f /tmp/db1.backup ends with hundreds of lines of errors and does…
Madoc Comadrin
  • 540
  • 3
  • 11
  • 28
1
2 3 4