@ wrote... (1 year, 10 months ago)

It turns out that after I imported a bunch of databases from mysql all the ownerships were wrong so any future django migrations would fail.

Based on this stackoverflow.com answer I made a quick script to mass change several databases and its tables all at once.

Become the postgres user via…

sudo -s -u postgres

Define some variables…

export user="db_user"
export dbs="db1 db2"

and then copy-paste the following to change the database, scheme, tables, sequences, and view ownership.

for db in $dbs; do
    psql -c "alter database $db owner to $user" $db;
done

for db in $dbs; do
    psql -c "alter schema public owner to $user" $db;
done

for db in $dbs; do
    tables=`psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" $db`

    for tbl in $tables; do
        psql -c "alter table \"$tbl\" owner to $user" $db;
    done;
done

for db in $dbs; do
    seqs=`psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" $db`

    for seq in $seqs; do
        psql -c "alter table \"$seq\" owner to $user" $db ;
    done;
done

for db in $dbs; do
    views=`psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" $db`

    for view in $views; do
        psql -c "alter table \"$view\" owner to $user" $db ;
    done;
done
Category: tech, Tags: database
Comments: 0
Click here to add a comment