
As of 9.5, Postgres JSONB data can be modified - Vorcin84
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#JSONB-modifying_operators_and_functions
======
mrfusion
What's the significance of this?

~~~
rpedela
This completes the feature set for JSON document storage so you can now use
Postgres instead of NoSQL solutions. However Postgres's replication story is
still a little weak compared to other solutions.

~~~
hendzen
What are current best practices for configuring replicated PG with hot
failover?

To be clear, what I want is: \- The ability to deploy a cluster of say, 5
replicated PostgreSQL instances \- All write transactions go to a single
leader, which replicates them to the other instances \- Reads can go to any
instance \- If a leader crashes the cluster will elect a new one without human
intervention, and no committed transactions will be lost.

I've always been unclear on how to do this with Postgres.

~~~
yukinon
I'd be interested to know this as well.

I've worked mostly with MariaDB Galera Cluster which is pretty effortless to
setup, but for PostgreSQL there doesn't seem to be any clear direction on how
to do the same things.

------
erkose
Since when do we capitalize the 'g' in PostgreSQL?
[http://www.postgresql.org/](http://www.postgresql.org/)

------
Kiro
So all this time people have been telling me to use Postgres instead of
MongoDB without the ability to edit fields in my documents? What other basic
functionality is it missing?

~~~
minimaxir
Ideally you should be using Postgres or any other relational database instead
of MongoDB _for technical reasons other than document storage_. (e.g. ACID)

Document storage in Postgres is a best-of-both-worlds approach (can store
documents w/ ACID), with advantages of MongoDB and fewer downsides. But it's
still relatively new functionality. (I've seen use cases of document storage
for logging in-database and other quick-write/read-rarely data that would be a
pain to normalize with consistent schema, which seems compelling)

------
thewhitetulip
I have read so much about postgres as a database, but sadly I have never been
able to actually download install and use it, have read many tutorials but
unfortunately it isn't as simple as installing mysql

~~~
jaddison
I can only assume that you are not using any mainstream *nix-based operating
system. Installing through package management is easy for both MySQL and
PostgreSQL.

~~~
thewhitetulip
I am using elementary OS and I have installed it many times, can you give me a
good tutorial about installing and configuring postgres? I want to use it with
golang for development of webapps. I am not trolling here btw, I am really
stuck with sqlite because of inability of installing postgrest :(

~~~
simoncion
I mean, with Gentoo Linux I had to do the following things:

* If I wanted to make Postgres listen on non-localhost, modify _listen_addresses_ in _postgresql.conf_.

* Add database access controls as required in _pg_hba.conf_. The comments in the file are illuminating, but one can examine [0] for more information. [1]

* Create a database with appropriate permissions, along with a user. [2]

* Start Postgres.

I remember having to do the equivalent of _all_ of those things every time I
had to install MySQL. :)

What -exactly- did you successfully do, what -exactly- did you fail to do?
Divulging that information will help guide assistance efforts. :)

[0] [http://www.postgresql.org/docs/9.4/static/auth-pg-hba-
conf.h...](http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html)

[1] For example, the following line would allow password-based access from
IPv4 localhost by the user "user" to the database "database":

    
    
      host database user 127.0.0.1/32 md5
    

[2] [http://www.postgresql.org/docs/9.4/static/sql-
createdatabase...](http://www.postgresql.org/docs/9.4/static/sql-
createdatabase.html) for CREATE DATABASE syntax (or you can use createdb
[http://www.postgresql.org/docs/9.4/static/app-
createdb.html](http://www.postgresql.org/docs/9.4/static/app-createdb.html) )
[http://www.postgresql.org/docs/9.4/static/sql-
createrole.htm...](http://www.postgresql.org/docs/9.4/static/sql-
createrole.html) for CREATE ROLE syntax (or you can use
[http://www.postgresql.org/docs/9.4/static/app-
createuser.htm...](http://www.postgresql.org/docs/9.4/static/app-
createuser.html) ) [http://www.postgresql.org/docs/9.4/static/sql-
grant.html](http://www.postgresql.org/docs/9.4/static/sql-grant.html) for
GRANT syntax.

~~~
thewhitetulip
Hey,

I use elementary OS and all I did was sudo apt-get install postgres

I have no idea what it installed and what it didn't, in the entire process
never once it asked me for a password and while connecting to the database it
says that invalid password and I can't reset password without connecting to
the database!! May I know your email ID? so I'll try to install it once and
tell you how it goes.

As of now I have everything installed plus the graphical client with the
elephant logo, but I just have no idea what the password is

~~~
simoncion
> ...the entire process never once it asked me for a password and while
> connecting to the database it says that invalid password and I can't reset
> password without connecting to the database!!

Well, because you have access to the file that causes Postgres to determine
whether or not you need to provide a password to access a given database (
_pg_hba.conf_ ), you _could_ simply change the authentication method to one
that doesn't ask for a password. :)

However. On every Postgres install I've ever used, there are the following
lines in _pg_hba.conf_ :

    
    
      # "local" is for Unix domain socket connections only
      local   all all trust
    

If we look carefully, we see that the Type, Database, User, and Method entries
are present, but the Address entry is not. The comment also talks about a Unix
domain socket connection.

Reading the "Connecting to a Database" section of the manual for psql [0] (the
official command-line Postgres client) tells us that if we omit the hostname,
psql attempts to connect over the Unix domain socket. So, try the following
things:

* psql

When that fails, because 'role "$MY_USERNAME" does not exist', try

* psql -U $DATABASE_USER

(The Postgresql database user is typically postgres.) If that fails for some
other reason, try

* sudo su - $DATABASE_USER

* psql

You should now be connected to Postgres.

If we enter _help_ , we see that we can enter "\?" for help with psql
commands. If we enter _\?_ and look through the list of commands, we should
see one that does exactly the thing that we're trying to accomplish.

Anyway, leave a message here to let me know how this all went, or to ask any
follow up questions.

[0] [http://www.postgresql.org/docs/9.4/static/app-
psql.html](http://www.postgresql.org/docs/9.4/static/app-psql.html)

~~~
thewhitetulip
Hey man, thank you very much, I was somehow able to connect to the psql
database :) i created a user, a database and successfully created and dropped
a table!

HN these days is full of elitist people who think the people who are
struggling with things are downright dumb, hence the multiple downvotes I got
on the earlier comment. It is because of people like you, my friend, that HN
is still a user friendly community :)

Thank you!!

~~~
simoncion
> ...I was somehow able to connect to the psql database...

Yeah? For posterity, mind detailing how did you do it?

> HN these days is full of elitist people who think the people who are
> struggling with things are downright dumb...

No. That's not what happened. You said:

> I have never been able to actually download install and use [Postgresql] ...
> unfortunately it isn't as simple as installing mysql[.]

 _Everyone_ who has installed both MySql and and Postgres know that you need
to do exactly the same things [0] -albeit in _slightly_ different ways to get
started from scratch. Initial configuration of MySql and Postgres is _equally_
complicated. [1] This makes your comment look like an anti-Postgres slam.

Cynical people might suspect that your original comment was the time-tested
"Man, thing X sucks, can't do Y, and is completely useless!" Internet tech-
support bait, but that ain't me, baby.

Over the years, I've found great profit in assuming that most widely-used
non-B2B software [2] is actually generally well designed and thought-out, and
that any inability to do a thing is a result of my lack of understanding or
familiarity rather than poor or difficult design of the software. Why?
Because, -frankly- such failures are pretty much always a result of my lack of
understanding.

[0] Configure listen IP, create a superuser account, set database access
permissions, create a database, start the daemon.

[1] That is, (compared to _writing_ software that uses SQL) not complicated at
all, and just as complicated as configuring _any_ other non-trivial daemon.

[2] And _some_ B2B software, too.

~~~
thewhitetulip
Hmm I guess so everyone thought that I was the troll who just hates X because
Y reason!!

I did a sudo -u suraj createdb mydb

It said that role suraj didn't exist, then I realized that postgres was using
my own username, I didn't touch a single config file! In a remarkable sense of
irony, postgres is so amazing that while installation mysql needs us to give a
password, postgres uses this user concept and for each database you have to
create a new user so there is no one password to rule them all.

$ sudo -u suraj psql

I was just trying to do

$ psql

then it was saying that the role suraj didn't exist

so I did sudo -u postgres createuser --superuser $USER

then it said that role suraj exists, then it said database suraj didn't exist

sudo -u suraj createdb amarokdb;

then it created the db

then

I connected the db using the usual format

~~~
simoncion
> ...postgres is so amazing that while installation mysql needs us to give a
> password...

If you install Mysql from scratch, you _also_ need to manually set the
superuser password.

For a different perspective, read the relevant post-installation configuration
documentation for Mysql [0] and Postgresql [1] on Gentoo Linux.

If your distro asks you -as part of the installation process- for a MySQL
password, that's a feature that has been added by the MySQL packager for your
distro, not a feature of MySQL.

> postgres uses this user concept and for each database you have to create a
> new user so there is no one password to rule them all.

This is _completely_ incorrect. Look at _pg_hba.conf_ and the comments
contained within, as well as the GRANT command I mentioned in an earlier
comment.

A suggestion:

Slow down. Block out an hour or two to learn how to properly manage both a
Postgres and a MySql server.

Figure out that Postgres calls "Users" "Roles" (because a Role can be one or
more users). Read about Database Role management in the official Postgres
documentation. Learn about Roles and Role permission management. Do the same
for MySQL.

Once you've done this you will

* Be able to manage DB user accounts and permissions without depending on random internet walkthroughs.

* Understand that -despite the terminology differences- managing users and DB permissions is fundamentally the same in MySQL and Postgres.

[0]
[https://wiki.gentoo.org/wiki/MySQL/Startup_Guide#MySQL_Insta...](https://wiki.gentoo.org/wiki/MySQL/Startup_Guide#MySQL_Installation)

[1]
[https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart#Starting_...](https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart#Starting_the_Server)

~~~
thewhitetulip
hey I downloaded the postgres documentation and begun reading it, thank you
very much for your guidance!

~~~
simoncion
Happy reading, dude.

FWIW, I've found the Postgres documentation to be a little less easy for a
novice to wrap their head around, but _substantially_ higher quality than the
MySQL documentation. Just remember that the Postgres documentation has
sections that are purely reference material and sections that are mostly
tutorials.

When in doubt, play around and make notes on what you did so when you
accidentally burn it all down you can remember to not do the same thing twice.
;)

