CLI tool to help with postgresql database schema updates, migrations and versioning.
bash
cargo install pgfine
bash
git clone https://gitlab.com/mrsk/pgfine
cargo install --path ./pgfine
env-local-db-0.sh (as an example) file like this:```bash
export PGFINECONNECTIONSTRING="..."
export PGFINEADMINCONNECTION_STRING="..."
export PGFINE_DIR="./pgfine" ```
Environment variables need to be activated when using pgfine. You can do this by running:
bash
source env-local-db-0.sh
To initialize pgfine project run:
bash
pgfine init
This will create directory for storing all pgfine project data:
./pgfine/
├── create
│ ├── 00-create-role.sql
│ └── 01-create-database.sql
├── drop
│ ├── 00-drop-database.sql
│ └── 01-drop-role.sql
├── functions
├── migrations
├── roles
├── tables
├── constraints
└── views
Modify newly created ./pgfine/create/*.sql and ./pgfine/drop/*.sql scripts if needed.
./pgfine/create/* scripts if needed.bash
pgfine migrate
Two extra tables will be created additionaly to the tables defined in the pgfine project:
pgfine_objects: contains a list of managed pgfine objects and their hashes.pgfine_migrations: Contains a list of executed migrations. Selecting the max value should reveal the current state of database. The first migration will be inserted as empty string../pgfine/**/*.sql../pgfine/migrations/* scripts../pgfine/views/public.view0.sql)<schema>.<name>.sql.Run
bash
pgfine migrate
Test your fresh db maybe.
Table constraints should be stored along with tables. You will have a problem if constraints form circular dependencies.
bash
pgfine migrate
Database objects are: - tables - views - indexes - constraints - functions - ...
Each database object has coresponding create/alter script in pgfine project directory (see bellow for details). Filenames must consist of schema name and object name and .sql extension (example: ./pgfine/tables/public.some_table_0.sql).
Database object scripts are executed when pgfine attempts to create or alter database object; except for tables - pgfine won't attempt to alter or drop tables, these changes have to be implemented using migration scripts.
Sometimes object needs to be dropped and created instead of updating it in place (one such case is when argument is removed from function definition). Drop script is generated using object id.
Example ./pgfine/tables/public.table0.sql:
```sql
create table table0 (
id bigserial primary key
);
-- create indexes -- create constraints -- create rules -- create triggers
```
Example ./pgfine/views/public.view0.sql:
```sql
-- it is recommended to include "or replace", otherwise it will be dropped and created again each time changes are made.
create or replace view view0 as
select t0.id
from table0 t0
join table1 t1 on t1.id = t0.id
-- create indexes maybe ```
In the constraint identifier the schema part should represent the schema of associated table. (Constraints do not dirrectly belong to particular schema of database, but they are associated with tables.)
When constraint is modified it will always be dropped and created again.
Example ./pgfine/constraints/public.table1_t0_id_fk.sql:
sql
alter table table1
add constraint table1_t0_id_fk foreign key (t0_id) references table1 (id);
pgfine initPGFINE_DIR.pgfine migrateCreates an up to date fresh databaes using PGFINE_ADMIN_CONNECTION_STRING and skips all migration scripts
PGFINE_CONNECTION_STRING credentials to connect to a target database../pgfine/migrations/ and inserts executed scripts into pgfine_migrations table.pgfine_objects table (or drop the object if it was deleted).pgfine_objects table with newest information.pgfine drop --no-jokePGFINE_ADMIN_CONNECTION_STRING credentials to connect to database./pgfine/drop/*.sql scripts to drop database and role.public schema../pgfine/migrations/ is assumed to be increasing in alphabetical order.CREATE OR REPLACE). If it fails we attempt to DROP (including dependencies if necesary) and CREATE a new version.If your database is huge it will probably take some time to execute migrations if those are involved in moving the data. Timeouts should be disabled in pgfine. If you find that it hangs in the middle of transaction you might check what is holding the locks, or just kill the pgfine process manually and retry again.
statement_timeout (integer)
Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.
Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.
idle_in_transaction_session_timeout (integer)
Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.
The default value of 0 disables this feature.
At the current stage pgfine is not the best thing in the world. You might also want to check these alternatives: - refinery - flyway - diesel.rs - dbmigrate - and more...
./pgfine/constraints)./example/PGFINE_DIRdatabase::migrate, database::drop, project::init