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
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.
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 :(
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. :)
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
> ...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.
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 :)
> ...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.
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
> ...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.
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. ;)
They already used "phone a friend" when they installed Python, so this time they are trying "ask the audience"; I can't wait to see them try and use "50/50" to install nginx.
I'd love to hear what issues you're facing (if you can remember any specifics). Its been a long time since I first installed PostgreSQL, but I also recall some difficulties. Some things have improved since (eg. it's no longer necessary to change shared memory kernel parameters) but some things are still pretty difficult (eg. correctly configuring pg_hba.conf).
Other things are just confusing to newcomers (eg. different meanings of the term "database" between MySQL and Postgres, or understanding schemas and search_path).
Hey, I installed it via the available things on ubuntu software center, I have the graphical utility installed but when I try to connect to it, it says invalid password and I have no idea what the default password is since it didnt ask me
I don't use OSX I use elementary OS and I installed everything but it says incorrect password and I have no damn clue what the default password is because while installation it didn't ask me for a password!
What documentation or tutorial were you following? The first thing you need to do is create a user (or act as the postgres user on your os) and if the doc or tutorial was an official one, it should be made more clear and you should file a bug, if it wasn't then you should contact the owner and tell them their tutorial isn't clear.
It's easy on any major OS. Windows and OS X have one/two-click installers, Linux distros and BSDs pre-package it. The only really difficult case is a less-mainstream OS where you'll have to compile it, which honestly isn't that hard either.
This is incorrect. None of these functions modify the value of a column. All of them are purely functional and return a jsonb value with the specified field(s) replaced.
The significance is one of performance and convenience: before, you would have had to construct a new jsonb object with only the field(s) you wanted modified changed. (In fact it is possible to define these new operators in terms of such; I have done so.) Now you can just use these handy built-in functions, which presumably perform better than the manual method as well.
I think you are using different interpretations of "you".
If you do:
update aTable
set loginInfo = loginInfo - 'lastLogin'
where 'loginInfo' is a jsonb column, you, the programmer, need not write out the parts of the data to keep. PostgreSQL still sees this as 'read column, evaluate expression, write result', so it will read and write the entire jsonb value.
This is only partially correct. These new features reduce the amount of data that must be transmitted to/from the postgres server and how much json must be parsed, but the full row is still written to the WAL (write ahead log). This is true even for partial updates to JSONB columns and even when only updating other non-JSONB columns in the row.
When you do need to update JSONB columns this is a big improvement. You still should consider the size of your JSONB columns and the number & frequency of updates to those rows.
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.
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.
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.
I am cheating and getting more or less the same availability properties by using multi-AZ RDS i.e. postgresql with AWS managing the synchronous block replication and standby promotion.
If someone is planning to use PostgreSQL as a NoSQL solution, one can use BDR (Bi-Directional Multi-Master Replication -> https://github.com/2ndQuadrant/bdr) and its global sequences (if needed).
There are plenty of other replication solutions available for Postgres, indeed.
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?
Why not read its extensive documentation (http://www.postgresql.org/docs/9.5/static/), evaluate its capabilities vs. those of MongoDB with respect to your particular use case and decide for yourself?
SQL has never ever been meant for "editing documents", doing so is certainly not considered "basic functionality" for anyone whose use case is best served by a RDBMS, and you'll likely be displeased by the performance of such in Postgres (or any other RDBMS) if that is something you consider "basic functionality".
(FWIW: what I've found to be the typical use case of JSON-in-SQL (or XML-in-SQL) is that of storing opaque JSON or XML blobs generated by some external service, which could perhaps benefit from indexed lookups within this data. If you're designing your main SQL database around JSON you're probably doing something wrong.)
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)
Other comments in the HN discussion indicate that 9.5 introduces an optimization to JSONB handling. It seems that you've always been able to edit fields in your documents, this just makes Postgres do less rewriting than before.
48 comments
[ 2.2 ms ] story [ 71.6 ms ] threadIt's also in most distro and bsd repos.
For the benefits of PostgreSQL over MySQL it is definitely worth learning.
* 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...
[1] For example, the following line would allow password-based access from IPv4 localhost by the user "user" to the database "database":
[2] http://www.postgresql.org/docs/9.4/static/sql-createdatabase... 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/sql-createrole.htm... 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/sql-grant.html for GRANT syntax.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
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:
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
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!!
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.
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
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...
[1] https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart#Starting_...
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. ;)
I find that to be the easiest way to get up and running with it.
Other things are just confusing to newcomers (eg. different meanings of the term "database" between MySQL and Postgres, or understanding schemas and search_path).
I got all the help from this thread and the above link!
The significance is one of performance and convenience: before, you would have had to construct a new jsonb object with only the field(s) you wanted modified changed. (In fact it is possible to define these new operators in terms of such; I have done so.) Now you can just use these handy built-in functions, which presumably perform better than the manual method as well.
If you do:
where 'loginInfo' is a jsonb column, you, the programmer, need not write out the parts of the data to keep. PostgreSQL still sees this as 'read column, evaluate expression, write result', so it will read and write the entire jsonb value.When you do need to update JSONB columns this is a big improvement. You still should consider the size of your JSONB columns and the number & frequency of updates to those rows.
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.
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.
There are plenty of other replication solutions available for Postgres, indeed.
SQL has never ever been meant for "editing documents", doing so is certainly not considered "basic functionality" for anyone whose use case is best served by a RDBMS, and you'll likely be displeased by the performance of such in Postgres (or any other RDBMS) if that is something you consider "basic functionality".
(FWIW: what I've found to be the typical use case of JSON-in-SQL (or XML-in-SQL) is that of storing opaque JSON or XML blobs generated by some external service, which could perhaps benefit from indexed lookups within this data. If you're designing your main SQL database around JSON you're probably doing something wrong.)
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)