Skip to content

Upload csv files to postgresql database

Upload csv files to postgresql database published on Комментариев к записи Upload csv files to postgresql database нет

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

postgresql script to find excess indexes

postgresql script to find excess indexes published on Комментариев к записи postgresql script to find excess indexes нет

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;

oracle how to get disk event waits estimates

oracle how to get disk event waits estimates published on Комментариев к записи oracle how to get disk event waits estimates нет

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;

postgresql configure separate directory for config files

postgresql configure separate directory for config files published on 1 комментарий к записи postgresql configure separate directory for config files

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'

Primary Sidebar

Яндекс.Метрика