How do I get the current unix timestamp from PostgreSQL?

Getting the Unix timestamp from a postgres timestamp with time zone like now() is simple, as you say, just:

select extract(epoch from now());

Things only get complicated when you have timestamp without time zone field (or just timestamp which is the same thing). When you put timestamp with time zone data like now() into that field, it will be converted to a particular timezone (either explicitly with at time zone or by removing the timezone) and the timezone information will be lost. If you only ever work in a single time zone this might not matter as conversion back to timestamp with time zone is clever enough to cope with DST in the current time zone. Example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz, 
       '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;


      timestamptz       |      timestamptz
------------------------+------------------------
 2011-03-27 00:59:00+00 | 2011-03-27 02:00:00+01

note the following that confused me earlier:

create table my_table(id integer, created timestamp);

\d my_table
              Table "stack.my_table"
 Column  |            Type             | Modifiers
---------+-----------------------------+-----------
 id      | integer                     |
 created | timestamp without time zone |

insert into my_table(id, created) 
values(1, '1970-01-01 00:00:00+00'),(2, '1970-01-01 00:00:00+00'::timestamptz);

select * from my_table;

 id |       created
----+---------------------
  1 | 1970-01-01 00:00:00
  2 | 1970-01-01 01:00:00
(2 rows)

select id, created, extract(epoch from created) from my_table;

 id |       created       | date_part
----+---------------------+-----------
  1 | 1970-01-01 00:00:00 |     -3600
  2 | 1970-01-01 01:00:00 |         0
(2 rows)

this is because "PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both [...] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type"