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)
PostgreSQL bloglarının tümü bu blogda toplanır.
"Why do I love Postgres?
- Because I'm not a DBA. "
apt-get install postgresql-9.3 (Debian)
yum install postgresql-server (Red Hat ailesi)
service postgresql initdb
chkconfig postgresql on
Create index index_1 on conference (presentation_id) where isactive = true;
Create unique index tests_success_constraint on tests (subject, target) where success;
Create index concurrently index_2 on users to_lower(email);
Create schema womentechmakers;
Create tablespace women location '/data/women';
Create index concurrently ariteknokent_idx on conference (speaker_id) tablespace women;
ACID , Savepoint , PITR ( WAL )
Rollback to savepoint my_svp;
Release savepoint my_svp;
"One of the nice things about Postgres is that it tries very hard to get you to do the right thing. MySQL doesn't care as much."
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 Van Yüzüncü Yıl Üniversitesi Bilişim Şenliği 2015 etkinliği için hazırlanmıştır. 6 Mayıs 2015'te sunulmuştur.
Staff Database Engineer @Xata, Main Organizer @Prague PostgreSQL Meetup, MSc, Computer and Systems Engineering @ Tallinn University of Technology, BSc, Applied Mathematics @Yildiz Technical University