personal blog
here is my drafts of how-to automate upload a lot of csv files to database ( use text fieds because there is no need to mach all types of columns in this case )
for i in $(ls -1 *.csv); do echo "create table $i (" ; echo $(head -1 $i | sed 's/,/ text,\n/g' ); echo "text );" ;echo copy $i | awk -F "." '{print $1}'; echo "from '/u01/import/$i' DELIMITER ',' CSV HEADER;" ; done
here how excess indexes can be found, excess mean that there is a single column index which is covered by first value of complex index
select * from (
select count(*),pg_size_pretty(pg_relation_size(t.table_name)),t.table_name,t.indkey_names[1],min(array_length(t.indkey_names,1)) min_index_elements_cnt from (
SELECT i.relname as indname,
i.relowner as indowner,
idx.indrelid::regclass table_name,
idx.indkey,
ARRAY(
SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
FROM generate_subscripts(idx.indkey, 1) as k
ORDER BY k
) as indkey_names,
idx.indexprs IS NOT NULL as indexprs,
idx.indpred IS NOT NULL as indpred
FROM pg_index as idx
JOIN pg_class as i
ON i.oid = idx.indexrelid
where idx.indrelid::text not like 'pg_toast%'
and pg_relation_size(indexrelid::regclass)>100*1024*1024 ) t
group by t.indkey_names[1],t.table_name
having count (*) >1
) tt where min_index_elements_cnt =1;
cat file | egrep -v '(^#|^\s*$|^\s*\t*#)'
base took from ASH Architecture and Advanced Usage — RMOUG
select event,round (est_dbtime_ms/
CASE WHEN est_waits=0 THEN est_dbtime_ms ELSE est_waits END
,1) as est_avg_latency_ms,time#
from (
select event,round(sum(case when time_waited >0 then greatest(1,1000000/time_waited) else 0 end )) as est_waits,
sum(1000) as est_dbtime_ms ,TRUNC( SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM SAMPLE_TIME), 1) /(24 * 60) as time#
from v$active_session_history where wait_class ='User I/O'
group by event,TRUNC( SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM SAMPLE_TIME), 1) /(24 * 60)
) order by 3 desc;
first of all we need to create directory for config files outside $PGDATA:
in my case this is /u01/postgres/9.5/main/conf/
next we need to modify service and add option with path of postgresql.conf
vim /usr/lib/systemd/system/postgresql-9.5.service
[Unit]
Description=PostgreSQL 9.5 database server
After=syslog.target
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
Environment=PGDATA=/u01/postgres/9.5/main/pgdata
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
ExecStartPre=/usr/pgsql-9.5/bin/postgresql95-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-9.5/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"
ExecStop=/usr/pgsql-9.5/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/pgsql-9.5/bin/pg_ctl reload -D ${PGDATA} -s
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
[Install]
WantedBy=multi-user.target
in config file we modify location of other config files:
data_directory='/u01/postgres/9.5/main/pgdata' hba_file='/u01/postgres/9.5/main/conf/pg_hba.conf' ident_file='/u01/postgres/9.5/main/conf/pg_ident.conf'