Category: Databases

Copy table in PostgreSQL

-- 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;

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"