Category: postgresql

I am trying to make a backup of a very big postgresql(around 165 gb) for two days now. and at last I find out my mistake. never take a dump at same disk. it eats a lot of IO and kill all services which depends to dumped database.

first try I am dumping database to same disk and after around 6 hours web server started to giving timeout and lovely sitescope mails 🙂 and I had to kill that process.

then I read a lot and started to dumping the database to another machine and it was smooth took around 4 hours to dump and no web server gave any timeout.

example commands

pg_dump -Fc dbname > db.backup

and I started restore like this

pg_restore -d dbname db.backup

before that I needed to recreate the db from psql. and this have not finished I must tell that pg_restore has -j parameter which gives more thread to read the dump file and you can give cpu number to -j which will work faster.

I know its been a long time I have not write anything. because I have not learn or done any new things. I dont want to write regular meaningless blogs here. anyway today I tried to restore a pg_dump out file to another postgresql server.

that pg_restore looks nice but in first try it started print lines of the file that was strange. at the end I learned that if we give the name of the database it will restore data to there. otherwise it works like scanner for a dump file.

here is the command: pg_restore -d dbName backupFileName

after that it does not print anything to stdout just starts restoring the data. if anyone knows how to show which table is being restored in that time that would be nice. because now I am cleaning some useless data with pgadmin manually.

at the end of any product there will be a reporting interfaces for counts. let say you build a advertisement site which gives people to publish their products on the site. they will want to see how many people visited their product in daily basis or weekly.

I used to do this in mysql like this

SELECT create_time as Date, count(id) as ‘Count’, FROM
views_of_product
group by date_format(create_time, ‘%d %m %Y’)
order by date_format(create_time, ‘%Y-%m-%d’) desc limit 7

this will nicely show last seven days views. but I needed to do same thing in postgresql. and like other days its not easily to find. I should check other report codes from project but no I allways research on google 🙂 anyway here is my code:

select to_char(created, ‘YYYY-MM-DD’) ,count(id)
from videos_capture
group by to_char(created, ‘YYYY-MM-DD’)
order by to_char(created, ‘YYYY-MM-DD’) desc limit 7

its not bad actually works like a charm but understanding this code is not that easy. after I found this I started to implement needs and see better and easy to understand solutions.

weekly count example. this will show new users weekly parts for last 3 months:

SELECT date_trunc(‘week’, created) AS “Week” , count(id) AS “New Users”
FROM users
WHERE created > now() – interval ‘3 months’
GROUP BY “Week”
ORDER BY “Week”

yearly example. this will show monthly 1 year users:

SELECT date_trunc(‘month’, created) AS “Month” , count(id) AS “New Users”
FROM users
WHERE created > now() – interval ‘1 year’
GROUP BY “Month”
ORDER BY “Month”

I must say understanding postgresql’s sql more easy then mysql.

14 bin satır bir schema dump ı var elimde pgadmin ile windowsda aldım başka bir windows makinede postgresql e bunu koyup bi şekilde yeni table oluşturmam lazım. tabii pgadmini açtım o makinede ama klasik çalışmadı pg_restore falan denedim yemedi. efenim örnek:
"c:Program FilesPostgreSQL8.2binpsql.exe" -Upostgres azbuz < "c:Documents and SettingsopakdilDesktopdump.backup"

sevimli oluo bu open source db ler.

bugünlerde yazılardan belli olduğu üzere postgre mıncıklıom sayfasında dolanırken kim geliştiri die bi link. grup süper 🙂 devrimde hemen yerini almış heyecanlı open source cu. asıl olay onun hizasındaki solundaki hatun ne güzelmiş bu db iyice ilgimi çekmeye başladı 😀