37

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran psql against that included the other files however I don't remember the syntax of this script.

xenoterracide
  • 2,921
  • 5
  • 31
  • 33

3 Answers3

45

Your script could look like this:

BEGIN;
\i file1.sql
\i file2.sql
COMMIT;

Or you could do something like this:

cat file1.sql file2.sql | psql -1 -f -
Peter Eisentraut
  • 10,723
  • 1
  • 35
  • 35
3

You could also simply do:

psql -c "BEGIN TRANSACTION;" -f file1.sql -f file2.sql -c "COMMIT;"

This will actually run in four different chained parts through the psql:

  1. Run BEGIN TRANSACTION;
  2. Run file1.sql
  3. Run file2.sql
  4. Run COMMIT;
caiohamamura
  • 131
  • 4
0

I did it using find in unix / linux with the path to the file sql past with rep_sql parameter with "read" instruction :

find -P ${rep_sql} -type f -name *.sql -execdir psql -d "mabase" -f {} +

It says: find not following links files whatever the name with ext as «.sql» and execute in the directory of the file psql working with mabase and execute the file found.

Deun
  • 1