Skip to main content
Database Administrators

Return to Answer

deleted 33 characters in body
Source Link
Evan Carroll
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

Three methods of doing this..

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql

Three methods of doing this..

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql
added 4 characters in body
Source Link
Evan Carroll
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

Three methods of doing this..

A psqlpsql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using findfind to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql

Three methods of doing this..

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql

Three methods of doing this..

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql
added 29 characters in body
Source Link
Evan Carroll
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

Three methods of doing this..

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

If you don't care aboutI'll show the index files, and you you can keep to a numeric sorting ordersecond method using find, you can make this even more structured and simple..

Using runfind to generate a master-partsload script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql

Three methods of doing this..

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables
  2. Or, just add them all to 01_mydb.sql

If you don't care about the index files, and you you can keep to a numeric sorting order, you can make this even more structured and simple..

Using run-parts

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql

Three methods of doing this..

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql
Source Link
Evan Carroll
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496