A normal trip for myself would mean driving down to SEA which is 90 minutes to 2 hours. This year, I decided on whim to see what it would take to fly out of YVR (Vancouver, B.C.) which is only 60 minutes driving.
Since I would be flying out of YVR on a non-connecting flight, I paid Canadian Dollars. For those that haven't been paying attention, the U.S. dollar has been doing very well lately (even overtaking the euro on some days). For example, the Canadian dollar is 27% cheaper right now than the U.S. dollar and thus my flight was 27% cheaper.
You can't connect to YVR, you must fly out of YVR. Therefore if you are in the aforementioned areas, you would fly into Seattle or Bellingham and then drive to YVR to connect to a new flight. Be patient and give yourself enough time (to not miss your flight), and you are going to save a lot of money.
Cheerio and make sure you register for my class!
There was Unix, Linux and Windows. They all run on hardware and that hardware all has bugs. What is the best way to work around hardware bugs? Backups.
You haven't had bad hardware, only bad developers? That's o.k., we have a solution to them too. It is called backups.
You haven't had bad hardware or bad developers, just bosses who still demand to have direct access to the data even though they haven't proven an ability to extract useful information without an extreme amount of hand holding? That's o.k., we have a solution to them too. It is called backups.
You haven't had any of the above? Lucky you! Can we move to your country or will you share whatever it is that you are putting in your tea? It sounds fantastic.
Either way, we have a solution to your data confidence, it is called backups and that is what this training is about.
postgres@sqitch:/# psql -U postgres psql (9.2.11) Type "help" for help. postgres=# create user foo; CREATE ROLE postgres=# \q
No biggy, we created a user foo as the super user postgres. All is good. However, what can that user foo do?
postgres@sqitch:/# psql -U foo postgres psql (9.2.11) Type "help" for help. postgres=> create table bar (id text); CREATE TABLE postgres=>
What? Yes. I connected to the postgres database as the unprivileged user foo. I then proceeded to create a table and as I own that table, I can insert data into that table. If we continue the example:
postgres=# \z bar Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+-------+-------------------+-------------------------- public | bar | table | | (1 row)
As noted above there are zero access privileges on the table bar. Now let's create a new unprivileged user, baz.
postgres=# create user baz; CREATE ROLE postgres=# \q
Now we reconnect as the user baz and try to insert data into table bar.
postgres@sqitch:/# psql -U baz postgres psql (9.2.11) Type "help" for help. postgres=> insert into bar values ('1'); ERROR: permission denied for relation bar postgres=>
The error represents exactly what should happen but I could just create a new table as user baz and start adding data. As a very simple example of why this could have undesirable results, try this as the user foo:
postgres@sqitch:/#psql -U foo postgres; psql (9.2.11) Type "help" for help. postgres=> insert into bar values (generate_series(1, 1000000000));
This is a topic that has come up in various ways over the years. After the long thread on pg_audit, I thought it might be time to bring it up again.
Contrib according to the docs is:
"These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system, mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness."
It has also been mentioned many times over the years that contrib is a holding tank for technology that would hopefully be pushed into core someday.
What I am suggesting:
1. Analyze the current contrib modules for inclusion into -core. A few of these are pretty obvious:
I am sure there will be plenty of fun to be had with what should or shouldn't be merged into core. I think if we argue about the guidelines of how to analyze what should be in core versus the merits of any particular module, life will be easier. Here are some for a start:
A. Must have been in contrib for at least two releases
B. Must have visible community (and thus use case)
2. Push the rest out into a .Org project called contrib. Let those who are interested in the technology work on them or use them. This project since it is outside of core proper can work just like other extension projects. Alternately, allow the maintainers push them wherever they like (Landscape, Github, Savannah, git.postgresql.org ...).
Why I am suggesting this:
1. Less code to maintain in core
2. Eliminates the mysticism of contrib
3. Removal of experimental code from core
4. Most of the distributions package contrib separately anyway
5. Some of core is extremely small use case (sepgsql, tsearch2, lo ...)
6. Finding utilities for PostgreSQL used to be harder. It is rather dumb simple teenage snapchat user easy now.
8. Isn't this what pgxs is for?
9. Everybody hates cleaning the closet until the end result.
10. Several of these modules would make PostgreSQL look good anyway (default case insensitive index searching with citext? It is a gimme)
11. Contrib has been getting smaller and smaller. Let's cut the cord.
12. Isn't this the whole point of extensions?
This is just the straight HTML dump that is generated from Docbook but since it is inline the links won't work. The current -devel docs are here and the updated version I am working is below:
PostgreSQL provides the program
pg_dump for generating a backup file
with SQL commands that, when fed back to the server, will recreate
the database in the same state as it was at the time of the dump.
The basic usage of pg_dump is:
pg_dump -C "REPLACEABLE">-F p "REPLACEABLE">-f "OPTION">outfile dbname
The use of -C ensures that the dump file
will contain the requisite CREATE
DATABASE command within the dump file. The use of "REPLACEABLE">-Fp ensures that
you are using the plain text format and the use of "REPLACEABLE">-f allows you to specify the name of the
file the dump will be written to. It is also possible for
pg_dump to create files in other
formats that allow for parallelism and fine-grained control of
object backup or restoration. For more details on all options
available to pg_dump please refer
to the pg_dump reference page.
The pg_dump application
requires read access to all objects within the database that it
will be operating with. This generally requires database super-user
access. It is possible for any database user to use pg_dump to
backup the objects that they own regardless of super-user access.
This can be achieved using options such as "REPLACEABLE">-n schema or
The primary advantage of using
"APPLICATION">pg_dump over the other backup methods
described is that pg_dump output
is architecture independent. A backup made with "APPLICATION">pg_dump can generally be moved between
operating systems and different architectures (32bit, 64bit, Sparc,
Intel). Whereas file-level backups and continuous archiving are
The text files created by
"APPLICATION">pg_dump are internally consistent, meaning,
the dump represents a snapshot of the database at the time
pg_dump began running.
pg_dump does not block other
operations on the database while it is working. (Exceptions are
those operations that need to operate with an exclusive lock, such
as most forms of ALTER TABLE.)
Note: Like any other "PRODUCTNAME">PostgreSQL client application, "APPLICATION">pg_dump will by default connect with the
database user name that is equal to the current operating system
user name. To override this, either specify the "REPLACEABLE">-U option or set the environment variable
The pg_dump application
provides other formats. The most notable are the use of "REPLACEABLE">-F c or
The use of the custom format ( "REPLACEABLE">-F c) is an
excellent option for smaller databases when you need fine grained
control of the objects you chose to restore. The use of the
directory format ( "REPLACEABLE">-F d) allows for
parallel connection based backups. If you are performing a backup
with many objects and using pg_dump then the "LITERAL">directory format will be the most efficient. This
option also allows for fine grained control of the objects you
chose to restore.
If PostgreSQL was built on a
system with the zlib compression
library installed, the custom dump format will compress data as it
writes it to the output file. This will produce dump file sizes
similar to using gzip, but it has the
added advantage that tables can be restored selectively.
Example 24-1. Backup a single table
pg_dump -U "OPTION">user "REPLACEABLE">-h host1 -F c -f outfile -t table1 dbname
Example 24-2. Using wildcards with table list
pg_dump -U "OPTION">user "REPLACEABLE">-h host1 -F c -f outfile -t table* dbname
Example 24-3. Using parallelism and a wildcard table
pg_dump -U "OPTION">user "REPLACEABLE">-h host1 -F d -f outfile -t table* -j 8 dbname
Note: The use of the custom or
directory "APPLICATION">pg_dump formats requires the use of "app-pgrestore.html">pg_restore and will not work with "app-psql.html">psql. There is
more information on using "APPLICATION">pg_restore in section "backup-dump.html#ADVANCED-RESTORE-PGDUMP">Section 24.1.3.
The psql application is the
default client that ships with "PRODUCTNAME">PostgreSQL. It is also the default application
used when restoring text based dumps created by the "APPLICATION">pg_dump application. For details information
on psql please see "app-psql.html">psql. For the
purposes of restoring a dump the basic usage is:
psql -f "OPTION">infile "REPLACEABLE">-d dbname
Note: If you omitted "REPLACEABLE">-C when executing "APPLICATION">pg_dump the CREATE
DATABASE command will not be in the text file. You will need
to create the database yourself from "LITERAL">template0 before the executing the restore (e.g.,
with createdb "REPLACEABLE">-T template0
pg_dump does not backup users,
Important: The dumps produced by "APPLICATION">pg_dump are relative to "LITERAL">template0. This means that any languages,
procedures, etc. added via template1 will
also be dumped by pg_dump. As a
result, when restoring, if you are using a customized "LITERAL">template1, you must create the empty database from
template0, as in the example above.
After restoring a backup, one should execute
"sql-analyze.html">ANALYZE on each database so the query
optimizer has useful statistics; see "routine-vacuuming.html#VACUUM-FOR-STATISTICS">Section 23.1.3
and Section 23.1.6
for more information. For more advice on how to load large amounts
of data into PostgreSQL
efficiently, refer to Section 14.4.
Example 24-4. Using pipes to restore to new server
pg_dump -h "OPTION">host1 "REPLACEABLE">-d "OPTION">dbname | psql "REPLACEABLE">-h "OPTION">host2 "REPLACEABLE">-d dbname
If one is using the custom,
"LITERAL">directory or tar formats
the restore command is "app-pgrestore.html">pg_restore. The "APPLICATION">pg_restore program has many benefits over the
use psql including fine grained
object restore and parallelism.
Example 24-5. Extracting a text dump from a custom format
The following will extract the backup to the standard output.
The use of -F is optional as
pg_restore should be able to
detect the format.
pg_restore -F "OPTION">c infile
Example 24-6. Restoring a single table
pg_restore -U "OPTION">username "REPLACEABLE">-h "OPTION">host1 "REPLACEABLE">-d "OPTION">dbname "REPLACEABLE">-t "OPTION">table infile
Example 24-7. Using parallelism to restore databases
The use of parallelism will normally allow databases to restore
much faster than a single connection based restore. The restore
will only execute as quickly as it can restore your largest table
but for databases with many objects it is the fastest "APPLICATION">pg_dump based restore.
pg_restore -U "OPTION">username "REPLACEABLE">-h "OPTION">host1 "REPLACEABLE">-d "OPTION">dbname "REPLACEABLE">-t "OPTION">table "REPLACEABLE">-j 8 "OPTION">infile
pg_dump dumps only a single
database at a time, and it does not dump information about roles or
tablespaces (because those are cluster-wide rather than
per-database). To support convenient dumping of the entire contents
of a database cluster, the "app-pg-dumpall.html"> "APPLICATION">pg_dumpall program is provided.
pg_dumpall backs up each database
in a given cluster, and also preserves cluster-wide data such as
role and tablespace definitions. The basic usage of this command
pg_dumpall > outfile
The resulting dump can be restored with "APPLICATION">psql:
psql -f infile postgres
It is necessary to have database superuser access when using a
pg_dumpall dump. The superuser
acess is required to restore the role and tablespace
pg_dumpall works by emitting
commands to re-create roles, tablespaces, and empty databases, then
invoking pg_dump for each
database. This means that while each database will be internally
consistent, the snapshots of different databases are not
Cluster-wide data can be dumped alone using the
"OPTION">--globals-only option. This is necessary to fully
backup the cluster if running the "APPLICATION">pg_dump command on individual databases.
Note: If you use tablespaces, make sure that the
tablespace paths in the dump are appropriate for the new
The act of backing up a normal sized database is relatively
simple. The act of backing up a large database (>500GB) can be
challenging. Fortunately, "PRODUCTNAME">PostgreSQL is very flexible in its ability to
provide a reliable backup. Here is a list of things you might want
to consider when backing up a large database.
Use the directory format and the
option. This will ensure the quickest and most flexible pg_dump
Use continuous archiving as described
in Section 24.2. You can
then backup the replica without putting load on the master.
Use pg_basebackupas described
The pg_dump methods utilize at least one connection if not many
The presentation was provided by Eric Worden who happens to be a CMD employee. The talk overall is very good and provided a lot of information that I didn't know about dates. It also lead to the development of a new PostgreSQL extension (more on that at a later time).
The most interesting part of the talk to me was the use of a dimensions table to make date queries much, much faster. Either he or I will be submitting a blog post on that feature alone.
If you are interested in seeing what he has to say you can visit us at the Whatcom PgDay being hosted at LinuxFestNorthwest this weekend!
It was one of those moments that you realize just how blessed of a life you have. A moment where you stop and realize that you must have done something right, at least once. I was with my all of my ladies, there were no other people at the camp site, the weather was clear and it was set to hit 68F. The only sound was the gentle lapping of water and the occasional goose.
It was at this time that I was able to finally take a step back and reflect on PgConf.US. This conference meant a lot to me professionally. I didn't organize it. I only spoke at it, Command Prompt sponsored, and I occasionally offered feedback to the conference committee (as it is run by PgUS) via PgUS board meetings. This conference has become everything (and more) I tried to make the United States PostgreSQL Conference series. It is a conference of the best, the brightest and most importantly the users of our beloved elephant. In the United States, it is "The" PostgreSQL Conference.
That isn't to say there aren't other PostgreSQL conferences in the states. There are at least two others that run, but somehow after this latest conference I feel they are destined to niche attendance. There is nothing wrong with that and frankly we need the niche conferences. They fill a hole, else people wouldn't attend. It is just that experiencing the level of effort and greatness that was created by Jonathan and Jim was truly something awesome to behold.
They aren't paid to run these conferences. They do it because they want to help our community. They do it for free and speaking as somebody who has run more PostgreSQL conferences than any other current PostgreSQL conference organizer in the U.S., it is a thankless job. It is a volunteer effort that everyone should take a moment to thank them for. Jonathan, Jimmy: Thank you for all your efforts. The community could not have had such a great conference without you.
I have only two constructive feedback points for the organizers:
Other than that, I have only praise for the conference. Command Prompt has already made back all of the money it spent on sponsoring and traveling. We acquired quite a few new customers and are exceedingly happy with the result. Expect us to sponsor again.
You should also expect that I will speak again. I gave away three excellent bottles of whiskey to attendees of my presentation. I enjoy speaking at this conference because this conference understands what a conference is really about.
What else would I say about the conference? I had a blast. I would definitely go again. I will do everything I can to help the excellent organization team grow not just 25%, but 50% next year. What if the community stepped up and planned on attending "PgConf U.S." in 2016? The community should step up, promote and advocate throughout every community they are involved in. It's worth it.
Let's get more of everyone involved and save them from the nefarious reaches of those "other" databases.
I also met a couple of people from CoinBeyond. They are a point-of-sale software vendor that specializes in letting "regular" people (read: not I or likely the people reading this blog) use Bitcoin!
That's right folks, the hottest young currency in the market today is using the hottest middle aged technology for their database, PostgreSQL. It was great to see that they are also located in Whatcom County. The longer I am here, the more I am convinced that Whatcom County (and especially Bellingham) is a quiet tech center working on profitable ventures without the noise of places like Silicon Valley. I just keep running into people doing interesting things with technology.
Oh, for reference:
I also submitted a talk entitled: "Suck it! Webscale is Dead; PostgreSQL is King!". This talk was submitted as a joke. I never expected it to be accepted, it hadn't been written, the abstract was submitted on the fly, improvised and in one take. Guess which talk was accepted? "Webscale is Dead; PostgreSQL is King!". They changed the first sentence of the title which is absolutely acceptable. The conference organizers know their audience best and what should be presented.
What I have since learned is that the talk submission committee was looking for dynamic talks, dynamic content, and new, inspired ideas. A lot of talks that would have been accepted in years past weren't and my attempt at humor fits the desired outcome. At first I thought they were nuts but then I primed the talk at SDPUG/PgUS PgDay @ Southern California Linux Expo.
I was the second to last presenter on Thursday. I was one hour off the plane. I was only staying the night and flying home the next morning, early. The talk was easily the best received talk I have given. The talk went long, the audience was engaged, laughter, knowledge and opinions were abound. When the talk was over, the talk was given enthusiastic applause and with a definite need for water, I left the room.
I was followed by at least 20 people, if not more. I don't know how many there were but it was more than I have ever had follow me after a talk before. I was deeply honored by the reception. One set of guys that approached me said something to the effect of: "You seem like you don't mind expressing your opinions". At this point, some of you reading may need to get a paper towel for your coffee because those that know me, know I will readily express an opinion. I don't care about activist morality or political correctness. If you don't agree with me, cool. Just don't expect me to agree with you. My soapbox is my own, rent is 2500.00 a minute, get in line. I digress, what did those guys ask me about? Systemd, I don't think they were expecting my answer, because I don't really have a problem with Systemd.
Where am I going with this post? I am stomping my way to PgConf.US with an updated version of this talk (You always learn a few things after giving a performance). I am speaking in the first slot on Friday and I am going to do everything I can to bring it. I can't promise to be the best, I can promise to do everything in my power to be my best. I am being recorded this time. My performance will be on the inner tubes forever. I have no choice.
A challenge, do you accept?
I challenge all speakers at this voyage of PgConf.US to take it up a notch. If you were accepted, you have a responsibility to do so. Now, now, don't get me wrong. I am not suggesting that you put on a chicken suit and Fox News t-shirt to present. I am however suggesting that if you are a monotone speaker, try not to be. If you are boring, your audience will be bored and that is the last thing the conference, you or the audience wants. So speak from your diaphragm, engage the audience and make their time worth it!
After VanLUG I spent the next morning at the Vancouver Aquarium with my ladies. Nothing like beautiful weather, dolphins and jelly fish to brighten the week. Once back in Bellingham, we moved on to a WhatcomPUG meeting where I presented, "Practical PostgreSQL: AWS Edition". It was the inaugural meeting but was attended by more than just the founders which is a great start!
I got to rest from community work on Wednesday and instead dug my head into some performance problems on a client High Availability Cluster. It is amazing that even with proper provisioning how much faster ASYNC rep is over SYNC rep. Some detailed diagnosis and proving data demonstrated, we switched to ASYNC rep and all critical problems were resolved.
On Thursday it was off to Southern California Linux Expo where I presented, "Suck it! Webscale is dead; long live PostgreSQL!". The room was packed, people laughed and for those who might have been offended, I warned you. Your offense is your problem. Look inside yourself for your insecurities! All my talks are PG-13 and it is rare that I will shy away from any topic. My disclosure aside, I had two favorite moments:
After I spent the evening with JimmyM (BigJim, my brother), Joe Conway of SDPUG/Credativ , Jim Nasby of the fledgling bird that is Blue Treble and the very enjoyable, I don't remember her name but she works at Enova (a well known PostgreSQL installation). Flying out the next morning at 8am probably should have been avoided though.
I am glad to be on the ground for the next few weeks before I head off to PgConf.US. It is looking like this conference is once again prove why PostgreSQL is King! Bring your people from all the lands, you are about to enter utopia.