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:
pgfine_objects: contains a list of managed pgfine objects and their hashes.
sql
create table if not exists pgfine_objects (
po_id text primary key,
po_type text,
po_md5 text,
po_script text,
po_path text,
po_depends_on text[],
po_required_by text[]
);
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.
sql
create table if not exists pgfine_migrations (
pm_id text primary key
);
./pgfine/**/*.sql../pgfine/migrations/* scripts../pgfine/views/public.view0.sql)<schema>.<name>.sql.Run
bash
pgfine migrate
Test your fresh db maybe.
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
```
Table constraints and indeces can be stored along with tables. But to modify them you will have to write migration scripts.
If you have circular foreign key dependencies you should define those constraints in a separate ./pgfine/constraints/ files to break the cycle.
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 ```
The schema part of constraint identifier 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);
Postgres allows you to have the same name constraints assigned to different tables. But pgfine will only work with uniquely defined constraints per schema.
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 and project contents.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)PGFINE_DIRdatabase::migrate, database::drop, project::initPGFINE_ROLE_PREFIX env variable to enable role per single databasepgfine_objects table maybe?.PGFINE_ALLOW_DROP variable to protect production envs./example/./pgfine/initial/ execute after the database is created ./pgfine/final/ execute after the database objects are created