A blog and website by Peter Bengtsson
10 December 2015
0 comments
PostgreSQL
tl;dr
Start psql with -E or --echo-hidden
I wanted to find out EVERYTHING that's related to a specific topic. Tables, views, stored procedures etc.
One way of doing that is to go into psql and type \d and/or \df and look through that list. But that's unpractical if it gets large and I might want to get it out stdout instead so I can grep and grep -v.
There are lots of Stackoverflow questions about how to SQL select all tables but I want it all. The solution is to start psql with -E or --echo-hidden. When you do that, it prints out what SQL it used to generate the output for you there. You can then copy that and do whatever you want to do with it. For example:
peterbecom=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
With this I was able to come up with this SQL select to get all tables, views, sequences and functions.
SELECT
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
WHEN 'f' THEN 'foreign table' END as "Type"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid);
SELECT
p.proname as "Name",
'function'
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema';
A usecase of this is that I put those two SQL selects in a file and now I can grep:
$ psql mydatabase < everything.sql | grep -i crap
31 October 2015
0 comments
PostgreSQL
We have lots of tables that weigh a lot. Some of the tables are partitions so they're called "mytable_20150901" and "mytable_20151001" etc.
To find out how much each table weighs you can use this query:
select table_name, pg_relation_size(table_name), pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2 desc limit 10;
It'll give you an output like this:
table_name | pg_relation_size | pg_size_pretty
--------------------------+------------------+----------------
raw_adi_logs | 14724538368 | 14 GB
raw_adi | 14691426304 | 14 GB
tcbs | 7173865472 | 6842 MB
exploitability_reports | 6512738304 | 6211 MB
reports_duplicates | 4428742656 | 4224 MB
addresses | 4120412160 | 3930 MB
missing_symbols_20150601 | 3264897024 | 3114 MB
missing_symbols_20150608 | 3170762752 | 3024 MB
missing_symbols_20150622 | 3039731712 | 2899 MB
missing_symbols_20150615 | 2967281664 | 2830 MB
(10 rows)
But as you can see in this example, it might be interesting to know what the sum is of all the missing_symbols_* partitions.
Without further ado, here's how you do that:
select table_name, total, pg_size_pretty(total)
from (
select trim(trailing '_0123456789' from table_name) as table_name,
sum(pg_relation_size(table_name)) as total
from information_schema.tables
where table_schema = 'public'
group by 1
) as agg
order by 2 desc limit 10;
Then you'll get possibly very different results:
table_name | total | pg_size_pretty
--------------------------+--------------+----------------
reports_user_info | 157111115776 | 146 GB
reports_clean | 106995695616 | 100 GB
reports | 100983242752 | 94 GB
missing_symbols | 42231529472 | 39 GB
raw_adi_logs | 14724538368 | 14 GB
raw_adi | 14691426304 | 14 GB
extensions | 12237242368 | 11 GB
tcbs | 7173865472 | 6842 MB
exploitability_reports | 6512738304 | 6211 MB
signature_summary_uptime | 6027468800 | 5748 MB
(10 rows)
You can read more about the trim() function here.
20 July 2015
0 comments
MacOSX,
PostgreSQL
When you're using PostgreSQL for local development it's sometimes important to get an insight into ALL SQL that happens on the PostgreSQL server. Especially if you're trying to debug all the transaction action too.
To do this on OSX where you have PostgreSQL installed with Homebrew you have to do the following:
1. Locate the right postgresql.conf file. On my computer this is in /opt/boxen/homebrew/var/postgres/ but that might vary depending on how you set up Homebrew. Another easier way is to just start psql and ask there:
$ psql
psql (9.4.0)
Type "help" for help.
peterbe=# show config_file;
config_file
--------------------------------------------------
/opt/boxen/homebrew/var/postgres/postgresql.conf
(1 row)
peterbe=#
Open that file in your favorite editor.
2. Look for a line that looks like this:
#log_statement = 'all' # none, ddl, mod, all
Uncomment that line.
3. Now if you can't remember how to restart PostgreSQL on your system you can ask brew:
$ brew info postgresql
Towards the end you'll see some files that look something like this:
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
4. To locate where PostgreSQL dumps all logging, you have to look to how Homebrew set it up. You can do that by opening ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist. You'll find something like this:
StandardErrorPath
/opt/boxen/homebrew/var/postgres/server.log
That's it. You can now see all SQL going on by running:
$ tail -f /opt/boxen/homebrew/var/postgres/server.log
Remember to reverse this config change when you're done debugging because that server.log file can quickly grow to an insane size since it's probably not log rotated.
Happy SQL debuggin'!
12 June 2014
2 comments
Python,
Mozilla,
PostgreSQL
Yesterday I had the good fortune to present Crontabber to the The San Francisco Bay Area PostgreSQL Meetup Group organized by my friend Josh Berkus.
To spare you having to watch the whole presentation I'm going to summarize some of it here.
My colleague Lars also has a blog post about Crontabber and goes into a bit more details about the nitty gritty of using PostgreSQL.
What is crontabber?
It's a tool for running cron jobs. It's written in Python and PostgreSQL and it's a tool we need for Socorro which has lots and lots of stored procedures and cron jobs.
So it's basically a script that gets started by UNIX crontab and runs every 5 minutes. Internally it keeps an index of all the apps it needs to run and it manages dependencies between jobs and is self-healing meaning that if something goes wrong during run-time it just retries again and again until it works. Amongst many of the juicy features it offers on top of regular "cron wrappers" is something we call "backfilling".
Backfill jobs are jobs that happen on a periodic basis and get given a date. If all is working this date is the same as "now()" but if something was to go wrong it remembers all the dates that did not work and re-attempts with those exact dates. That means that you can guarantee that the job gets started with every date possible even if it needs to catch up on past dates.
There's plenty of documentation on how to install and create jobs but it all starts with a simple pip install crontabber.
To get a feel for how you write crontabber "apps", checkout the ones for Socorro or flick through the slides in the PDF.
Is it mature?
Yes! It all started in early 2012 as a part of the Socorro code base and after some hard months of it stabalizing and maturing we decided to extract it out of Socorro and into its own project on GitHub under the Mozilla Public Licence 2.0 licence. Now it stands on its own legs and has no longer anything to do with Socorro and can be used for anything and anyone who has a lot of complicated cron jobs that need to run in Python with a PostgreSQL connection. In Socorro we use it primarily for executing stored procedures but that's just one type of app. You can also make it call out on to anything the command line with a "@with_subprocess" decorator.
Is it finished?
No. It works really well for us but there's a decent list of features we want to add. The hope is that by open sourcing it we can get other organizations to adopt it and not only find bugs but also contribute code to add more juicy features.
One of the soon-to-come features we have in mind is to "internalize locking". At the moment you have to wrap it in a bash script that prevents it from being run concurrently. Crontabber is single-threaded and we don't have to worry about "dependent jobs" starting before "parent jobs" because the depdendencies and their state is stored in the database. But you might need to worry about the same job (the one due next) to be started concurrently. By internalizing the locking we can store, in the state database, that a particular job is being started on and thus not have to worry about it starting the same job twice.
I really hope this project can grow and continue to support us in our needs.
30 April 2014
0 comments
PostgreSQL
If you run Homebrew on your OSX and you use that to install postgres you will have noted there's a new formula for Postgres 9.3(.4). Yay! (actually this was done many months ago but I'm slow to keep my brews upgraded)
When you run the upgrade you'll notice that psql no longer works because the server can't start.
Bummer! But there's hope: This excellent blog post
That's all you need. ...unless you have some uncompatible library extension installed. E.g. json_enhancements
The problem is that you can't install json_enhancements into a Postgres 9.3 server (json_enhancements is a backport from 9.3 to desperate people still on 9.2). And you can't do the upgrade because the new server has one less installed library. You'll get this after a failing pg_upgrade:
peterbe@mbp:~$ cat loadable_libraries.txt
Could not load library "$libdir/json_enhancements"
ERROR: could not access file "$libdir/json_enhancements": No such file or directory
I left some more details about this on the pgsql-admin mailing list.
The trick that worked for me was to start the old 9.2 server like this:
/usr/local/Cellar/postgresql/9.2.2/bin/postgres -D /usr/local/var/postgres -p 9000
And now I can open psql against the old data[base] and drop the extension:
$ psql -p 9000 mydatabase
psql (9.3.4, server 9.2.2)
mydatabase=# drop extension json_enhancements;
After I had done that I was able to successfully complete the pg_upgrade.
I hope this helps some other poor sucker stuck in the same chicken and egg situation.
UPDATE
A much better blog post to guide you is Ketia's Blog. For example, here's a post about upgrading from 9.4 to 9.5.
