-- create new table CREATE TABLE new_table (LIKE old_table INCLUDING all); -- delete default sequence from primary key ALTER TABLE new_table ALTER ot_id DROP DEFAULT; -- copy data from old to new table INSERT INTO new_table SELECT * FROM old_table; -- change primary key column name to new one ALTER TABLE new_table RENAME COLUMN ot_id TO nt_id; -- create new sequence - auto increment CREATE SEQUENCE new_table_nt_id_seq; -- set max value for this sequence as max ot_id from old table SELECT setval('new_table_nt_id_seq', (SELECT max(ot_id) FROM old_table), true); -- set this sequence as default value for new table primary key ALTER TABLE new_table ALTER nt_id SET DEFAULT nextval('new_table_nt_id_seq'); -- set new table primary key as owner of this sequence ALTER SEQUENCE new_table_nt_id_seq OWNED BY new_table.nt_id;
Category: PostgreSQL
Restore postgresql db from dump
script.sh
#!/bin/bash if ["$(whoami)" != "root"]; then echo "Start script as root root!" exit 1 fi _DBNAME="$1" while [ -z "$_DBNAME" ]; do echo "Write DB name!" read _DBNAME done _DBUSER="$2" while [ -z "$_DBUSER" ]; do echo "Write DB username!" read _DBUSER done _PATH="$3" while [ -z "$_PATH" ]; do echo "Write path to DB dump file!" read _PATH done service postgresql restart su - postgres -c "dropdb $_DBNAME" su - postgres -c "createdb $_DBNAME -O $_DBUSER" su - postgres -c "psql $_DBNAME < $_PATH"