Gülçin Yıldırım Jelínek
Staff Database Engineer @Xata, Main Organizer @Prague PostgreSQL Meetup, MSc, Computer and Systems Engineering @ Tallinn University of Technology, BSc, Applied Mathematics @Yildiz Technical University
Liste | Tanımı |
pgsql-admin | PostgreSQL yönetimi |
pgsql-general | Kullanıcılar için genel bir tartışma alanı |
psql-announce | Postgres ve ona bağlı servis duyuruları |
psql-performance | Postgres performansına ilişkin konular |
Planet PostgreSQL blogunu takip edin. (2ndQuadrant,pgExperts,VMware,OmniTI,EnterpriseDB, EndPoint,credativ,Cybertec,CommandPrompt,OpenSCG)
"Why do I love Postgres?
- Because I'm not a DBA. "
apt-get install postgresql-9.3
Create index index_1 on conference (presentation_id) where isactive = true;
Create index index_2 on users to_lower(email);
Create index concurrently index_3 on users to_lower(email);
Create schema womentechmakers;
Create tablespace women location '/data/women';
Create user devfest with password 'i<3postgres!';
Create role devfestw with password 'i<3linux!'
Create role devfestwomen with password 'i<3linux!' login;
gulcin=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------+--------------
devfest | | {}
devfestw | Cannot login | {}
devfestwomen | | {}
gulcin=# Create role gdg;
CREATE ROLE
gulcin=# Grant gdg to devfest;
GRANT ROLE
gulcin=# Grant gdg to devfestw;
GRANT ROLE
gulcin=# Grant gdg to devfestwomen;
GRANT ROLE
gulcin=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------+--------------
devfest | | {gdg}
devfestw | Cannot login | {gdg}
devfestwomen | | {gdg}
gdg | Cannot login | {}
Create role gdg option;
SUPERUSER | NOSUPERUSER |
CREATEDB | NOCREATEDB |
CREATEROLE | KNOCREATEROLE |
CREATEUSER | NOCREATEUSER |
INHERIT | NOINHERIT |
LOGIN | NOLOGIN |
REPLICATION | NOREPLICATION |
gulcin=# Alter role gdg superuser;
ALTER ROLE
gulcin=# \du List of roles Role name | Attributes | Member of -------------+-------------------------+------------ gdg | Superuser, Cannot login | {}
gulcin=# Alter role gdg nosuperuser;
ALTER ROLE
gulcin=# \du List of roles Role name | Attributes | Member of -------------+-------------------------+------------ gdg | Cannot login | {}
Grant select,update on table table_1 to gdg;
Grant insert on all tables in schema public to devfestwomen;
Grant usage on table_1_id_seq to devfestw;
Grant select on all sequences in schema google to devfest;
Revoke select,update on table table_1 from gdg;
Revoke insert on all tables in schema public from devfestwomen;
Revoke usage on table_1_id_seq from devfestw;
Revoke select on all tables in schema google from devfest;
gulcin=# explain select * from table_1;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on table_1 (cost=0.00..4740.30 rows=86430 width=140)
(1 row)
gulcin=# explain analyze select * from table_1;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on table_1 (cost=0.00..4740.30 rows=86430 width=140)
(actual time=0.274..35.983 rows=86430 loops=1)
Total runtime: 40.492 ms
(2 rows)
Begin;
Explain analyze delete from table_1;
Rollback;
Explain (format JSON) select * from table_1;
"When elephants fight it is the grass that suffers."
"Filler dövüşür çimenler ezilir."
tail -f postgresql-Fri.log | perl -pe 's/.*ERROR.*/\e[1;31m$&\e[0m/g'
Select * from pg_available_extensions;
Select * from pg_available_extension_versions;
gulcin=# Create extension pg_stat_statements;
CREATE EXTENSION
gulcin=# Select * from pg_stat_statements;
ERROR: pg_stat_statements must be loaded via shared_preload_libraries
gulcin=# Show config_file;
config_file
-----------------------------------------
/var/lib/pgsql/9.2/data/postgresql.conf
(1 row)
gulcin=# vim /var/lib/pgsql/9.2/data/postgresql.conf
#---------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------
# - Memory -
shared_preload_libraries ='pg_stat_statements'#(change requires restart)
gulcin=# select * from pg_stat_all_tables where relname = 'table_1';
-[ RECORD 1 ]-----+-------------------------------------------------
relid | 28193
schemaname | public
relname | table_1
seq_scan | 30686799 /* okuma ile ilgili parametreler */
seq_tup_read | 28979969846 /* okuma ile ilgili parametreler */
idx_scan | 2541503146 /* okuma ile ilgili parametreler */
idx_tup_fetch | 1119955873 /* okuma ile ilgili parametreler */
n_tup_ins | 129 /* kaç kayıt eklenmiş */
n_tup_upd | 123199 /* kaç kayıt güncellenmiş */
n_tup_del | 0 /* kaç kayıt silinmiş */
n_tup_hot_upd | 122323
n_live_tup | 1011
n_dead_tup | 58
last_vacuum | 2014-03-01 06:03:44.979917+02
last_autovacuum | 2014-03-01 17:08:39.960498+02
last_analyze | 2014-03-01 06:03:45.003016+02
last_autoanalyze | 2014-03-01 17:15:39.937703+02
vacuum_count | 184
autovacuum_count | 591
analyze_count | 185
autoanalyze_count | 754
gulcin=# select * from pg_stat_statements where query like '%table_1%';
(No rows)
Loglara bakarım, yetkilerini alırım. Kaldırırım.
#-------------------------------------------------
# ERROR REPORTING AND LOGGING
#-------------------------------------------------
# - Where to Log -
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
# - When to Log -
log_min_duration_statement = 500
# - What to Log -
log_checkpoints = on
log_duration = off
log_line_prefix = '%t [%v]-[%p]: [%l-1] user=%u,db=%d ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without ms
# %m = timestamp with ms
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
log_lock_waits = on # log lock waits >=deadlock_timeout
log_timezone = 'Turkey'
#----------------------------------------------
# RUNTIME STATISTICS
#-----------------------------------------------
# - Query/Index Statistics Collector -
track_activities = on
track_counts = on
track_functions = pl # none, pl, all
SELECT pg_stat_activity.datname,
pg_class.relname,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
substr(pg_stat_activity.query,1,10),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) AS "age",
pg_stat_activity.pid
FROM pg_stat_activity,
pg_locks
LEFT
OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE pg_locks.pid=pg_stat_activity.pid
AND MODE LIKE 'Exclusive%'
AND datname= 'warehouse'
ORDER BY query_start;
Select * from pg_stat_activity where pid = 0001
Select pg_terminate_backend(0001);
Adı | Dili |
PL/Java | Java |
PL/PHP | PHP |
PL/R | R |
PL/Ruby | Ruby |
PL/sh | Unix shell |
"PostgreSQL: making very hard things possible, and simple things hard."
"PostgreSQL: çok zor şeyleri mümkün, çok basit şeyleri zor kılar."
gulcin# crontab -l
00 02 * * * sh /home/postgres/scripts/getbackup.sh
0 05 * * * sh /home/postgres/scripts/daily_vacuum_analyze.sh
#WAL Archive CleanUp
0 02 * * * sh /var/lib/pgsql/removewal_archive.sh
crontab -e // crontab dosyasını düzenlemeye yarar.
# * * * * * çalıştırılacak komut
# ┬ ┬ ┬ ┬ ┬
# │ │ │ │ │
# │ │ │ │ │
# │ │ │ │ └───── haftanın günü (0 - 7) (0'dan 6'ya Pazar'dan Cumartesi'ye demektir; 7 Pazar, 0 da Pazar.)
# │ │ │ └────────── ay (1 - 12)
# │ │ └─────────────── ayın günü (1 - 31)
# │ └──────────────────── saat (0 - 23)
# └───────────────────────── dakika (0 - 59)
exec &> /tmp/devfestwomen.log
echo -e "Script Started at\t`date`\n\n"
drop_database() # dbname
{
echo " "
echo "Restoring the database \"$1\""
echo " "
psql -U postgres -d postgres -c "ALTER DATABASE $1 CONNECTION LIMIT 0;"
psql -U postgres -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity where datname='$1';"
psql -U postgres -d postgres -c "DROP DATABASE $1;"
}
create_database() # dbname,owner,dumpfile
{
psql -U postgres -d postgres -c "CREATE DATABASE $1 OWNER $2 TABLESPACE new_tablespace;"
psql -U postgres -d postgres -c "ALTER DATABASE $1 CONNECTION LIMIT 0;"
psql -U postgres -d postgres -c "COMMENT ON DATABASE $1 IS 'DB updated at `date`'; "
pg_restore -U postgres -j 4 -x -O --no-tablespaces -d $1 $3
}
open_database() # dbname
{
psql -U postgres -d $1 -c "ANALYZE;"
psql -U postgres -d postgres -c "ALTER DATABASE $1 CONNECTION LIMIT -1;"
}
reload_devfestwomen()
{
pg_dump -U postgres -h 127.0.0.1 -F c -b -f "/tmp/devfestwomen.dump" devfestwomen
drop_database devfestwomen
create_database devfestwomen postgres /tmp/devfestwomen.dump
psql -U postgres -d devfestwomen < /scripts/devfestwomen_extra.txt
open_database devfestwomen
}
reload_devfestwomen
ls -lh /tmp/*.dump
cat /tmp/devfestwomen.log | mail -s "DevFest Women Log" yildirim.gulcin88@gmail.com
rm -f /tmp/devfestwomen.dump
apt-get install git // Debian tabanlı dağıtım Ubuntu gibi.
yum install git // RHEL
yum install git-core // Fedora vs.
ssh-keygen -t rsa -C "yildirim.gulcin88@gmail.com"
cat ~/.ssh/id_rsa.pub
git config --global user.name "Gulcin Yildirim"
git config --global user.email "yildirim.gulcin88@gmail.com"
mkdir devfestwomen
cd devfestwomen
git init
touch README
git add README
git commit -m 'Welcome to DevFest Women 2014!'
git remote add origin git@demo.gitlab.com:gitlab/devfestwomen.git
git push -u origin master
psql -h 127.0.0.1 -U gulcin -d database_1
\l ve \l+
\c
\dt ve \dt+
\di ve \di+
\d ve \d+
\du
\dp
\df
\db
\x
\q
\?
ls -la
ls -lh
pwd
locate
which
df -kh
free -g
htop/top/atop
ping
traceroute
mount
tail
less
vim
nano
cat
scp
rsync
cp
mv
rm -rf
mkdir
rmdir
iostat
ifconfig
By Gülçin Yıldırım Jelínek
Bu sunum DevFest Women 2014 için hazırlanmıştır.
Staff Database Engineer @Xata, Main Organizer @Prague PostgreSQL Meetup, MSc, Computer and Systems Engineering @ Tallinn University of Technology, BSc, Applied Mathematics @Yildiz Technical University