PostgreSQL - query from bash script as database user 'postgres'
Try this one:
#!/bin/bash
psql -U postgres -d database_name -c "SELECT c_defaults FROM user_info WHERE c_uid = 'testuser'"
Or using su
:
#!/bin/bash
su -c "psql -d database_name -c \"SELECT c_defaults FROM user_info WHERE c_uid = 'testuser'\"" postgres
And also sudo
:
#!/bin/bash
sudo -u postgres -H -- psql -d database_name -c "SELECT c_defaults FROM user_info WHERE c_uid = 'testuser'"
Run postgres query from another server, in bash
Try
PGPASSWORD=yourpass psql -U postgres -W -h 1.2.3.4 -d testdb -p 5432
See: https://www.postgresql.org/docs/9.3/static/libpq-envars.html
or ~/.pgpass file https://www.postgresql.org/docs/9.3/static/libpq-pgpass.html
Run a psql query from a shell script
psql -U postgres ctfdb
Connects as user postgres
. You are connecting to the DB as your shell username root
.
There are other ways such as setting the PGUSER
environment variable or configuring pg_hba.conf
to allow access for other users.
Shell script to execute pgsql commands in files
First off, do not mix psql
meta-commands and SQL
commands. These are separate sets of commands. There are tricks to combine those (using the psql meta-commands \o
and \\
and piping strings to psql in the shell), but that gets confusing quickly.
- Make your files contain only SQL commands.
- Do not include the
CREATE DATABASE
statement in the SQL files. Create the db separately, you have multiple files you want to execute in the same template db.
Assuming you are operating as OS user postgres
and use the DB role postgres
as (default) Postgres superuser, all databases are in the same DB cluster on the default port 5432 and the role postgres
has password-less access due to an IDENT
setting in pg_hba.conf
- a default setup.
psql postgres -c "CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8'
TEMPLATE template0"
I based the new template database on the default system template database template0
. Basics in the manual here.
Your questions
How to (...) run a set of pgsql cmds from file
Try:
psql mytemplate1 -f file
Example script file for batch of files in a directory:
#! /bin/sh
for file in /path/to/files/*; do
psql mytemplate1 -f "$file"
done
The command option -f
makes psql
execute SQL commands in a file.
How to create a database based on an existing template at the command line
psql -c 'CREATE DATABASE my_db TEMPLATE mytemplate1'
The command option -c
makes psql
execute a single SQL command string. Can be multiple commands, terminated by ;
- will be executed in one transaction and only the result of the last command returned.
Read about psql command options in the manual.
If you don't provide a database to connect to, psql
will connect to the default maintenance database named "postgres". In the second answer it is irrelevant which database we connect to.
store postgresql result in bash variable
Put the -c
option just before its argument - the query. Mind also using the additional -t
option to get just the tuple value. And of course, use the backticks (`) operator.
Using the -X
option is also recommended, as sometimes a .psqlrc
file might add some redundant output, as well as the -A
option, which disables column aligning (whitespaces).
In order to skip NOTICE or other additional messages, include the -q
flag.
vartest=`psql -d $db -U $user -AXqtc "SELECT gid FROM testtable WHERE aid='1'"`
Checking the result of a postgres query from Bash
The contents of userexists
are likely to actually contain the linebreaks, but you probably checked the contents with an unquoted expansion:
# Assignment
$ userexists=' __row__
---------
1
(1 row)'
$ echo "$userexists" # Quoted expansion
__row__
---------
1
(1 row)
$ echo $userexists # Unquoted expansion squashes whitespace
__row__ --------- 1 (1 row)
Additionally, if you want to compare two strings containing linebreaks with [ ... ]
, you can't use "\n"
to insert a linebreak. ANSI-C escapes, $'\n'
, would work:
var='has
linebreak'
[ "$var" = $'has\nlinebreak' ] && echo 'Match'
prints Match
. Two remarks: Bash will understand ==
when used in [ ]
, but it's not portable, so =
is recommended. Also, [ ]
doesn't support pattern matching, so the *
at the end of your right-hand side won't do what you expect. [[ ]]
and case
can be used for pattern matching.
To avoid the whole problem in the first place, you can supply a few flags to psql
, specifically:
--quiet
– no informational output-t
– tuples only; no column names, result count footers etc.-A
– unaligned output (removes leading blank from line)
and the return from your command will be just 1
and you can compare with
if [ "$userexists" = 1 ]; then
Run a shell script when a database record is written to postgres
Write a trigger function using PL/sh. That's what it's for.
Related Topics
Join Two Tables Based on Relationship Defined in Third Table
Select Latest Records by Datetime Field
Join Tables on Columns of Composite Foreign/Primary Key in a Query
What Does "Select Count(1) from Table_Name" on Any Database Tables Mean
What Is Wrong with a Transitive Dependency
SQL Server Select Top 10-20 Results
What Is the Easiest Way to Update an Image Field with the Content of a File
How to Generate a Random, Unique, Alphanumeric Id of Length N in Postgres 9.6+
Syntax Error in Dynamic SQL in Pl/Pgsql Function
How to Copy Data from One Table to Another in Postgres Using Copy Command
Rollback Event Triggers in Postgresql
Importing .SQL File on Windows to Postgresql
SQL Query for Finding a Value in Multiple Ranges