How to Execute a Series of Commands in PSQL

postgresqlpostgresql-9.5psql

I am looking for a solution to execute multiple commands in PSQL.

Lets say I have a file with 2 name for database.

1. slave1
2. slave2

I already have a master template named "master_db".

If I would do it manually, I will do the following:

CREATE DATABASE slave1 TEMPLATE master_db;
CREATE DATABASE slave2 TEMPLATE master_db;

If there were only 2, it would be very easy but in my case there are more than 30, hence I would like to know if we can create a script. I did try to search but couldn't find a solution.

The databases are in the same cluster, if that matters.

Best Answer

If you are using psql 9.6+, there is a very convenient command called \gexec:

Sends the current query input buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed. [...] The generated queries are executed in the order in which the rows are returned, and left-to-right within each row if there is more than one column. NULL fields are ignored. The generated queries are sent literally to the server for processing, so they cannot be psql meta-commands nor contain psql variable references.

So, your script could look like:

SELECT format('create database slave%s template master_db', l_num) 
  FROM generate_series(1, 30) t(l_num);

\gexec

Note that you can use the newer psql version, even if the server version is lower. There might be some rare cases of incompatibility (when using \d* commands, because some system views may change between server versions), but I haven't yet seen such a case.

If you are still stuck on an earlier client version, you can just use the SELECT and copy the output manually to a script file.

If your database names are in a file, you have multiple options. Either treat that file with an editor (something like sed -i -e 's/^/CREATE\ DATABASE\ /' -e 's/$/\ TEMPLATE\ master_db' in any OS where sed exists), and then run the resulting script.

Alternatively, you can also do something like this:

CREATE TEMPORARY TABLE db_names (db text);

\copy db_names FROM 'path/to/your/file'

SELECT format('create database slave%s template master_db', db) 
  FROM db_names;

\gexec

Important note: only use \gexec when you absolutely trust the source you are working with. The above approach is prone to SQL injection (which, interestingly, not so easy when one of the commands on the same line is CREATE DATABASE, but it's always better to be careful than sorry later).

Related Question