Handy breedbase db migration commands

gistfile1.txt

# skips first sp_person_id to exclude admin account
psql -U postgres -h breedbase_db <db_name> -c "\copy (SELECT * FROM sgn_people.sp_person where sp_person_id > 1) TO STDOUT" > people.tsv
psql -U postgres -h breedbase_db <db_name> -c "COPY sgn_people.sp_person FROM STDIN" < people.tsv

# Grant usage on all schemas to web_usr
DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO web_usr $$, sch);
    END LOOP;
END;
$do$;

# Grant select, update, insert, delete on all tables in all schemas to web_usr
DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace
    LOOP
        EXECUTE format($$ GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA %I TO web_usr $$, sch);
    END LOOP;
END;
$do$;

# Grant select, update, usage on all sequences in all schemas to web_usr
DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace
    LOOP
        EXECUTE format($$ GRANT SELECT, UPDATE, USAGE ON ALL SEQUENCES IN SCHEMA %I TO web_usr $$, sch);
    END LOOP;
END;
$do$;

# To remove example project data and reset project sequences:
breedbase=# delete from projectprop where project_id = 1;
DELETE 1
breedbase=# delete from project where project_id = 1;
DELETE 1
breedbase=# alter sequence project_project_id_seq restart with 1;
ALTER SEQUENCE
breedbase=# alter sequence projectprop_projectprop_id_seq restart with 1;
ALTER SEQUENCE


# To remove example location data and reset location sequence:
breedbase=# delete from nd_geolocation where nd_geolocation_id = 10;
DELETE 1
breedbase=# alter sequence nd_geolocation_nd_geolocation_id_seq restart with 1;
ALTER SEQUENCE