I am mikek's Blog

The personal blog of Mike Karthauser, who you may know from Brightstorm Limited or the internet.

Exporting results of a query as sql in postgres

Expanding on last weeks post, below is a way to get results from a certain query out into a file,

create table TMP_TABLE as select * from foo;

pg_dump -h localhost -U DATABASE_USERNAME --data-only --column-inserts -t TMP_TABLE DATABASE_NAME >OUTPUT_FILE_NAME ;

essentially, create a new table with your query in it and then use pg_dump to get it into a file.

Advertisements

Filed under: development, , , ,

Slashdot Architecture – How the Old Man of the Internet Learned to Scale

Slashdot effect: overwhelming unprepared sites with an avalanche of reader’s clicks after being mentioned on Slashdot. Sure, we now have the “Digg effect” and other hot new stars, but Slashdot was the original. And like many stars from generations past, Slashdot plays the elder statesman’s role with with class, dignity, and restraint. Yet with millions and millions of users Slashdot is still box office gold and more than keeps up with the young’ins. And with age comes the wisdom of learning how to handle all those users. Just how does Slashdot scale and what can you learn by going old school?

Read more at High Scaleability

Filed under: development, , ,

Postgres restart

Following on from yesterday, and being a postgres cli noob, heres a copy and paste method to kill a postgres server.

sudo su postgres -c '/opt/local/lib/postgresql82/bin/pg_ctl -D /opt/local/var/db/postgresql82/defaultdb stop'

repeat this with start in the appropriate place to restart.

Filed under: development, ,

Exporting table schema in postgres

Heres how to export a specific table schema in postgres.

pg_dump -h localhost -p 5432 -U USER_NAME -F p -s -s -v -f “/full_path/to/file.sql” -t ‘TABLE_NAME’ DB_NAME

replace USER_NAME, TABLE_NAME, DB_NAME and ‘/full_path/to/file’ accordingly.

BTW we did find an issue with using relative paths on mac osx (ie ~/file.sql) so if you are getting errors check that.

Filed under: development, ,

Making the change

Recently the BBC announced plans to build a new infrastructure.

Below repeats the architectural principals they are planning to adhere to:

Architectural principles

  1. Each project must have a clear customer and deliver a real benefit.
  2. Don’t Repeat Yourself (DRY). Life is too short to spend your time re-inventing things.
  3. Be as simple as possible. Just do what we need to do now.
  4. Be as open as possible. Assume that all services can be accessed from outside the [your company], by default.
  5. Start simple, then iterate. Build the smallest thing you could possibly need, deploy it, then build applications on top of it. Think building blocks, not monoliths.
  6. Don’t optimise prematurely. The service might not grow the way we think it will.
  7. Build to scale. Think stateless, think content delivery networks, think database resilience.
  8. Test often. So you know when you need to optimise. So you can maintain your code. So you can maintain your platform.
  9. Evolve. Teams, systems, support structures. The platform. These principles!
  10. Let it die. Be prepared to turn your system off, or change it unrecognisably.

Filed under: infrastructure, ,

Wilkommen

Forays into wordpress, no less and the start of a (mostly) tech blog.

Filed under: Uncategorized,

what's hot

Flickr Photos

Sponsor Mike run the Bath Half marathon

mikek on twitter