Postgres psql

$ psql -U postgres

[スーパーユーザ作成]
create user admin with password 'casio00' superuser;
select * from pg_user;

select * from pg_shadow;

\g

 select * from pg_user where usename = CURRENT_USER;         # 現在のユーザ

[データベース作成]
select * from pg_database;
create database ARCSDBMS owner admin;
creatte database ZABBIX_MON owner zabbix_admin;
\l

drop database "ARCSDBMS";

[ データベースに接続]
\c zabbix_mon zabbix_admin

\c ARCSDBMS admin

[テーブル一覧]

\dt

\dt ARCSDBCB.*

[表領域の作成]
\! sudo mkdir /data
\! sudo chown postgres:postgres /data
\! sudo chmod 700 /data

\! sudo mkdir /data/arcsdbms_tbl

\! sudo chown postgres:postgres /data/arcsdbms_tbl

\! sudo chmod 700 /data/arcsdbms_tbl

\! sudo mkdir /data/arcsdbcb_tbl
\! sudo chown postgres:postgres /data/arcsdbcb_tbl
\! sudo chmod 700 /data/arcsdbcb_tbl

\! sudo mkdir /data/arcsdbcb_idx
\! sudo chown postgres:postgres /data/arcsdbcb_idx
\! sudo chmod 700 /data/arcsdbcb_idx

\! sudo mkdir /data/arcsdbss_tbl
\! sudo chown postgres:postgres /data/arcsdbss_tbl
\! sudo chmod 700 /data/arcsdbss_tbl

\! sudo mkdir /data/arcsdbss_idx
\! sudo chown postgres:postgres /data/arcsdbss_idx
\! sudo chmod 700 /data/arcsdbss_idx

create tablespace arcsdbcb_tbl owner admin location '/data/arcsdbcb_tbl';
create tablespace arcsdbcb_idx owner admin location '/data/arcsdbcb_idx';

create tablespace arcsdbss_tbl owner admin location '/data/arcsdbss_tbl';
create tablespace arcsdbss_idx owner admin location '/data/arcsdbss_idx';

create tablespace arcsdbcs_tbl owner admin location '/data/arcsdbcs_tbl';
create tablespace arcsdbcs_idx owner admin location '/data/arcsdbcs_idx';

create tablespace arcsdbpa_tbl owner admin location '/data/arcsdbpa_tbl';
create tablespace arcsdbpa_idx owner admin location '/data/arcsdbpa_idx';

【表領域の削除】

drop tablespace arcsdbms_tbl;

drop tablespace arcsdbcb_tbl;
drop tablespace arcsdbcb_idx;

drop tablespace arcsdbss_tbl;
drop tablespace arcsdbss_idx;

drop tablespace arcsdbcs_tbl;
drop tablespace arcsdbcs_idx;

drop tablespace arcsdbpa_tbl;
drop tablespace arcsdbpa_idx;

【ユーザの作成】

create user arcsdbcb with password 'casio00' ;

create user arcsdbss with password 'casio00' ;

create user arcsdbcs with password 'casio00' ;

create user arcsdbpa with password 'casio00' ;

※ 一時的にスーパユーザ           # for create table with tablespace

alter role arcsdbcb  with  superuser;

alter role arcsdbcb with nosuperuser;      # 解除

【スキーマの作成】

\c ARCSDBMS admin

\dn

create schema   arcsdbcb  authorization arcsdbcb;

create schema   arcsdbss  authorization arcsdbss;

create schema   arcsdbpa  authorization arcsdbpa;

create schema   arcsdbcs  authorization arcsdbcs;

drop schema   arcsdbcb;

drop schema   arcsdbss;

drop schema   arcsdbpa;

drop schema   arcsdbcs;

\dn

※スキーマ内にテーブル作成

create schema  arcsdbcb create table ccc001 (id integer);

[shell]

\!

【Django DB作成】

create database Users;

create database office;

create database shop;

create database sales;

create user admin with password  'password'; 

grant all privileges on database Users to admin;

grant all privileges on database office to admin;

grant all privileges on database shop to admin;

grant all privileges on database sales to admin;

※コマンド実行

psql -U admin -h 192.168.210.201 <<__EOF__

create database Users;

create database office;

create database shop;

create database sales;

create user admin with password  'password'; 

grant all privileges on database Users to admin;

grant all privileges on database office to admin;

grant all privileges on database shop to admin;

grant all privileges on database sales to admin;

__EOF__

------------------------------------以下参考---------------------------------------------------------

【参考】
#SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs arcsdbco arcsdbtm arcsdbtp vrcsdbcb xrcsdbcb"
SCHEMA_LISTS="xrcsdbcb"


#----------------------------------------
# Main ルーチン
#----------------------------------------
main(){
case $1 in
new )
new
;;

drop )
drop
;;

user )
user
;;

tablespace )
tablespace
;;

all )
init
create
report
;;

ALL )
ALL
;;

init )
init
;;

create )
create
;;

report )
report
;;

copy )
copy
;;

save )
save
;;

*)
echo "usage: run.sh init"
echo "usage: run.sh create"
echo "usage: run.sh report"
echo "usage: run.sh copy"
echo "usage: run.sh all ( without copy )"
;;

esac
}

new(){
psql -h 192.168.210.201 -p 5432 -U postgres << __EOF__
create user test_admin with password 'casio00' superuser;
select * from pg_user;
create tablespace test_arcsdbms_tbl owner test_admin location '/data/test_arcsdbms_tbl';
create database test_arcsdbms owner test_admin tablespace test_arcsdbms_tbl ;
select * from pg_database;
\c test_arcsdbms test_admin
select * from pg_user where usename = CURRENT_USER;
--\! sudo mkdir /data
--\! sudo chown postgres:postgres /data
--\! sudo chmod 700 /data

--\! sudo mkdir /data/test_arcsdbms_tbl
--\! sudo chown postgres:postgres /data/test_arcsdbms_tbl
--\! sudo chmod 700 /data/test_arcsdbms_tbl

--\! sudo mkdir /data/test_arcsdbcb_tbl
--\! sudo chown postgres:postgres /data/test_arcsdbcb_tbl
--\! sudo chmod 700 /data/test_arcsdbcb_tbl

--\! sudo mkdir /data/test_arcsdbcb_idx
--\! sudo chown postgres:postgres /data/test_arcsdbcb_idx
--\! sudo chmod 700 /data/test_arcsdbcb_idx
create tablespace test_arcsdbcb_tbl owner test_admin location '/data/test_arcsdbcb_tbl';
create tablespace test_arcsdbcb_idx owner test_admin location '/data/test_arcsdbcb_idx';

create user test_arcsdbcb with password 'casio00' ;
create schema test_arcsdbcb authorization test_arcsdbcb;
__EOF__
}

drop(){
psql -h 192.168.210.201 -p 5432 -U postgres << __EOF__
\c test_arcsdbms test_admin
drop schema test_arcsdbcb;
drop user test_arcsdbcb;
drop tablespace test_arcsdbcb_tbl;
drop tablespace test_arcsdbcb_idx;
\c postgres postgres
drop database test_arcsdbms;
drop tablespace test_arcsdbms_tbl;
drop user test_admin;
__EOF__
}

ALL(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
echo time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
echo time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_TABLE.sql
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_PARTITION.sql
cd ..
done
}

init(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
#time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
#time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
#psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_TABLE.sql
#psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_PARTITION.sql
cd ..
done
}

create(){
for sc in ${SCHEMA_LISTS}; do
echo start create table $sc
cd $sc
cdir=`pwd`
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cd ..
done
}

report(){
for sc in ${SCHEMA_LISTS}; do
echo start create report $sc
cd $sc
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cd ..
done
}

copy(){
for sc in ${SCHEMA_LISTS}; do
echo start copy $sc
cd $sc
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_TABLE.sql
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_PARTITION.sql
cd ..
done
}


save(){
tar cvzf arcsdbms.tar ${SCHEMA_LISTS}
}


main $@