PostgreSQL database reset

1
2
3
4

systemctl stop postgresql
rm -fr /var/lib/postgres/data
su -l postgres -c "initdb --locale=C.UTF-8 --encoding=UTF8 -D '/var/lib/postgres/data'"
systemctl start postgresql

1
2
3
4
5
6

cat >/tmp/_sql<<-OEF
create database mydb;
create role myuser with login password 'mypass';
grant all privileges on database mydb to myuser;
OEF
su -l postgres -c "psql -f /tmp/_sql"

1
2
3
4

# /usr/bin/pg_dump -h $HOST --encoding UTF8  $DB | gzip > mydb-$(date +%Y%m%d).gz
su -l postgres -c "cat /tmp/mydb.gz | gunzip | psql -d mydb"
# if there is no need for higher access, the above could be executed as myuser
# cat /tmp/mydb.gz | gunzip | psql -U myuser -d mydb