How to run all postgres sql scripts from same folder using shell script

Question

How do i write a shell script that runs postgres sql scripts stored in one folder sequentially.Example

1)dump.sql,

2)store.sql,

3)merge.sql,

4)import.sql

are to run sequentially in the same order and all the scripts are in the same folder. How do i write a shell script that performs the tasks sequentially.

Answer 1

Inside psql you can use "\i " to load a script. But also "\ir ".

So in your case create a load-all.sql file in the same directory as your other files..

contents load-all.sql:

\ir dump.sql
\ir store.sql
\ir merge.sql
\ir import.sql

inside psql:

psql# \i /path/to/file/load-all.sql

Answer 2

You can create a simple script e.g run.sh and the define variables corresponding to your environment. Here is a simple script that is running 2 sql files which are present on Desktop

PG_HOME=/usr/pgsql-12
PGUSER=postgres
DATABASE=postgres
PORT=5432
FILES_HOME=/home/edb/Desktop

$PG_HOME/bin/psql -U $PGUSER  -d $DATABASE -p $PORT -f $FILES_HOME/dump.sql
$PG_HOME/bin/psql -U $PGUSER  -d $DATABASE -p $PORT -f $FILES_HOME/store.sql

Answer 3

There are database schema migration tools available to help manage this process.

Examples include:

• Flyway
• Liquibase

I use Flyway. It does exactly what you want, connects to the database and runs a bunch of SQL scripts found in a folder. The order of execution is determined by the file names named using a certain convention.

On the first run, Flyway adds its own table to your database to store the history of what SQL scripts have been run. On subsequent runs, Flyway knows what SQL scripts have already been applied (and should be skipped) and which scripts are fresh (and should be applied).

Answer 4

Try this:

param([string]$directory = "")

$files = Get-ChildItem $directory
for ($i=0; $i -lt $files.Count; $i++) {
    $scriptName = $files[$i].Name
    psql -U <username> -d <database> -h <host> -p <port> -f $directory\$scriptName
}

directory is an argument you should pass to the script. So executing it with:

./sqlScript.ps1 C:\Users\<username>\Desktop\<pathToDict>

If you are troubling with executing psql on windows environment you can get help with this:

How to start psql.exe