\set ECHO queries2. execute the script using the -e flag
psql -e -f myfile.sql
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
\set ECHO queries2. execute the script using the -e flag
psql -e -f myfile.sql
psql -h hostname -U username -d databaseOptions:
Option Description -h <host> Hostname or IP address of the PostgreSQL server -p <port> Port number (default is 5432) -U <username> Username to connect as -d <dbname> Database name -W Prompt for password (useful if password auth is required) -f <filename> Execute commands from a SQL script file -c "<command>" Execute a single SQL command and exit -v var=value Set psql variable (used inside scripts) --set=var=value Same as -v, more explicit --dbname=<connstr> Full connection string, e.g.: postgresql://user:pass@host:port/dbnameExample
psql -h pg01.oric.no -p 5432 -U myuser -d mydbAlternatively, use an URL, which is more scripting friendly:
psql "postgresql://replicate@pg01.oric.no:5432/mydb"
\connect mydb \prompt 'Enter value for querid: ' v_quid \echo You entered: :v_quid SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id = :'v_quid';Run it:
sql -h pgserver1.oric.no -U dbadmin -d postgres -f find_rest_of_statement.sql Enter value for querid: -7644745457188850826 You entered: -7644745457188850826 query_sql_text ---------------------------------------------------------------------------------------------------------------------- THE QUERY TEXT WILL SHOW UP HERE :-)
SELECT 'drop table ' || table_schema || '.' || table_name ||';' FROM information_schema.tables WHERE table_schema = 'myschema' AND table_name like '%2020%' AND table_type = 'BASE TABLE';I put it in a file called gen_drop.sql
psql testdb01 -f gen_drop_tab.sql -L gen_drop_tab.logThe file output will show header and row count, like this:
?column? ----------------------------------------------- drop table myschema.tabA_2020_09; drop table myschema.tabB_2020_09; drop table myschema.tabC_2021_05; (27 rows)
psql testdb01 -f gen_drop_tab.sql -o drop_tab.sql -t
set trimspool on set pages 0 set heading off set feedback off set verify off set echo off
psql mydb psql (11.11) Type "help" for help.
mydb=# \timing Timing is on. mydb=# \timing Timing is off.
proddb01=# \dn List of schemas Name | Owner --------------------------+---------- sales | postgres hr | postgres manufacturing | postgres public | postgres (4 rows)Turn off unneccessary output like this:
proddb01=# \t Tuples only is on.Try again:
proddb01=# \dn sales | postgres hr | postgres manufacturing | postgres public | postgres
postgres=# \connect proddb01 You are now connected to database "proddb01" as user "postgres". proddb01=# \conninfo You are connected to database "proddb01" as user "postgres" via socket in "/var/run/postgresql" at port "5432". proddb01=#
proddb01=# \i yourfile.sql
proddb01=# psql proddb01 -f yourfile.sql
proddb01=# psql proddb01 -f test.sql -o test.log
proddb01=# psql proddb01 -f test.sql -L test.log
psql -h myserver.mydomain.com -U music musicdb or psql -h 127.0.0.1 -U music musicdb
[postgres@myserver.mydomain.com ~]$ psql psql (11.7) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=# \c mydb jim myserver.mydomain.com 5432 Password for user jim: You are now connected to database "mydb" as user "jim" on host "myserver.mydomain.com" at port "5432".
[postgres@myserver.mydomain.com ~]$ psql psql (11.2) Type "help" for help. postgres=# \connect musicdb You are now connected to database "musicdb" as user "postgres". musicdb=# \conninfo You are connected to database "musicdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".