Latest News

PostgreSQL How-to Change Owner for All Tables

  • Ivan Ivanov
  • PostgreSQL
PostgreSQL How-to Change Owner for All Tables

When moving websites between servers the database user often changes. While it is easy to dump and restore database dumps using pg_dump and pg_restore this might lead to invalid table ownership's on the new host. This bash script will easily fix this issue.


/usr/local/bin/alter


#!/bin/bash

if [ "$EUID" -ne 0 ]
then echo "This program should be run with sudo: sudo alter "
exit
fi

if test $# -lt 2; then
echo "Usage: sudo $0 "
exit 0
fi

database=$1
new_owner=$2
tables=`psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" $database`

for tbl in $tables ; do
psql -c "alter table $tbl owner to $new_owner" $database ;
done

Usage:


sudo alter unixsys unixsys
56 seconds