As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped me.
I look at Postgres not as a product to be mastered such as a new IDE or email client app. I see it more as a computer system unto itself, to be explored bit by bit. Some areas I may never touch (PostGIS, geo data), while others are vital to me (UUID data type, XML data type, date-time data types).
➥ So I see "learning Postgres" not as a goal to be accomplished but as an on-going process.
The more I learn about Postgres, the more impressed and awed I become. It took some investment of time and effort, but now I ♥ 🐘 (the Blue Elephant).
When in doubt, go with Postgres’ defaults.
One exception to the 'smart defaults' tip: Almost always best to specify UTF-8 as the character encoding. Do so when installing Postgres, as this defines the default when you later create databases. When you do create databases, verify that
UTF-8 is the encoding is in use.
Postgres is designed for security as a priority. By default you can only connect from a process/app running locally on the same machine. You must change the security configuration to connect from across a local network or internet.
To get started you must install Postgres. The main 3 ways to do that:
On some hosted servers such as Heroku or Amazon, the host may offer to spin up Postgres on your behalf. So no installation required in that case.
Postgres is a black-box, hiding your data inside. To access, you must use a utility tool to make a connection to the Postgres daemon process. For getting started, use either of these tools usually included with your Postgres installation.
- pgAdmin for GUI app access
- psql for command-line access
When playing around or experimenting with Postgres, I suggest using a virtual machine so you can later throw it away. Postgres is a pretty heavy installation, creating a new Unix-level user. Entirely removing Postgres can be a bit tricky.
I use Parallels 9, 10, and 11 for virtual machines running Mac OS X Mountain Lion or El Capitan to install various versions of Postgres 9.*. On a Mac, besides Parallels, you might be able to use VMware Fusion or Oracle VirtualBox.
Another route is a cloud server. Renting by the hour can be quite inexpensive, perhaps in the range of a penny (USD $0.01) or nickel or dime an hour. You should be able to delete your virtual machine when done or when you want to start from scratch with a fresh one. Examples include DigitalOcean, Heroku, and Amazon.
Find a PUG. Not a dog, a Postgres User Group. Search MeetUp.com and search this list.
Attend every meeting. Let discussions wash over you even when you cannot understand it all. Postgres has many features, add-ons, related products. Postgres has much depth. So don't expect to master Postgres, just keep looking for ways to use it.
If you cannot find one near you, start one.
Learn the names of some of the Postgres experts who are generous about sharing their knowledge. Pay special attention when they speak. For example, Craig Ringer on this page. Other examples, Josh Berkus, Bruce Momjian, David E. Wheeler, Selena Deckelmann.
You can find one-day "pgDay" events as well as multi-day Postgres conferences all over the world. For example, France, Prague, India, Finland, and Singapore. See this list.
In North America the multi-day conferences include:
You can often find a Postgres booth at other geek events as well.
For example, visit me while volunteering at the SeaPUG booth next April at LinuxFest Northwest 2016 in Bellingham WA, USA (outside Seattle). Postgres consulting companies have had booths in the past.
Read Postgres Weekly, a collection of select Postgres-related articles and news. Curated by an expert, Craig Kerstiens. Published weekly, thus the name.
Subscribe to the Postgres mailing lists.
I find it enlightening to skim through the weekly PostgreSQL Weekly News email delineating that week's work including a list of patches submitted. Posted on the Announce list. All the nitty-gritty details are beyond my comprehension, but nevertheless I become more and more familiar. I learn a bit about the many different features inh Postgres. And I learn how seriously the team takes their work, with an emphasis on solid quality.
Planet Postgres, blog portal
Planet Postgres is a consolidator of Postgres-related blogs.