Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i Have 2 different database schema in PostgreSQL sql file from pg_dump, is there automated way to generate alter table commands so it would be in sync, without inspecting one by one?

this is the example dump data:

database #1 (the correct one)

CREATE TABLE active_students (
    id integer NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    deleted_at timestamp without time zone,
    logs text DEFAULT ''::text,
    log_count integer DEFAULT 0,
    krs_confirmed_at timestamp without time zone,
    krs_delivered_at timestamp without time zone,
    krs_generated_at timestamp without time zone,
    krs_timestamp character varying(250) DEFAULT ''::character varying,
    krs_revision integer DEFAULT 1,
    khs_delivered_at timestamp without time zone,
    khs_generated_at timestamp without time zone,
    khs_timestamp character varying(250) DEFAULT ''::character varying,
    khs_revision integer DEFAULT 1,
    ips double precision DEFAULT 0.0,
    ipk double precision DEFAULT 0.0,
    sks integer DEFAULT 0,
    sks_total integer DEFAULT 0,
    user_id integer NOT NULL,
    semester_id integer NOT NULL,
    prodi_id integer
);


CREATE INDEX index_active_students_prodi ON active_students USING btree (prodi_id);

ALTER TABLE ONLY student_invoices
    ADD CONSTRAINT student_invoices_active_student_fk FOREIGN KEY (active_student_id) REFERENCES active_students(id);

database #2 (the current database for production)

CREATE TABLE active_students (
    id integer NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    deleted_at timestamp without time zone,
    logs text DEFAULT ''::text,
    log_count integer DEFAULT 0,
    krs_confirmed_at timestamp without time zone,
    khs_delivered_at timestamp without time zone,
    khs_generated_at timestamp without time zone,
    ips double precision DEFAULT 0,
    ipk double precision DEFAULT 0,
    sks integer DEFAULT 0,
    user_id integer NOT NULL,
    semester_id integer NOT NULL,
    sks_total integer DEFAULT 0,
    khs_timestamp character varying(250) DEFAULT ''::character varying,
    krs_delivered_at timestamp without time zone,
    krs_generated_at timestamp without time zone,
    krs_revision integer DEFAULT 1,
    khs_revision integer DEFAULT 1,
    prodi_id integer,
    krs_timestamp character varying(250) DEFAULT ''::character varying
);
share|improve this question
1  
You are doing it the wrong way round. Never change the DB model "manually". Every change should be scripted in a SQL script which in turn is stored in your version control system. Then you only need to apply the necessary scripts in production (after testing them). An even better alternative is to use a tool like Liquibase or Flyway to manage all that. –  a_horse_with_no_name Jan 2 at 17:13
add comment

1 Answer 1

You should use Liquibase to manage your database schema changes. You can write the schema in SQL (it was all xml back in the day). It's pretty darn easy once you get the hang of it. You can then keep all schema changes in source control. Bitbucket is free and private for small groups.

In the meantime though, you can use Liquibase to see what the differences are, and generate change scripts, in an automated fashion.

http://www.liquibase.org/documentation/diff.html

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.