Postgres is powerful, scalable, old and new. After a turn around in the industry, people dug out this hidden gem when we are entering a world with high demand for scalability and more importantly, open-source and free database services.But it’s still an alien for a lot of beginners. Here I will explain how do you use it after first install the database instance in your machine.
Part 1: Structure
First of all, what you install is called
database cluster , a
cluster(bunch) of databases. Postgres allows many databases existed in the same storage area.Yes! We are doing Clustering!You actually don’t have to do too much to it at the very beginning. Let’s keep diving inside.The next level is the database. Here is where most activities happened.Inside the database, we have the weirdest thing here: schema. Postgres added an extra layer of namespace inside the database to increase the granularity level. It really has no more meanings than just a bunch of tables. It provides a concern separation between groups of tables, so you can manage who with what authority can do what on the separated table space without interfering and cluttering other table spaces.The “schema” is named
tablespace somewhere else.Bear with it, naming is always a problem in computer science. Here is a graph illustrating the relationships:
Simply each level object can have multiple lower level objects. The database cluster sits in the top and it can have multiple databases. Then the database can own numerous schemes and each schema containing multiple tables, the very last level but the real space where keeping all your data.Let’s assume you install in a linux VM. Actually you can install in MacOS/Windows as well but in most production cases we choose Linux as a stable container. After you done the install the first thing you need to know is how to manage your Postgres. The tool is call
psql (shorten for PostgreSql) and you can use it from switch to the unix user
postgres or use it from other users via
peer auth. I will skip this part for now(you can google “how to use use psql).After you connected to Postgres via psql, you are sitting at database cluster level. The commands you enter here should be about managing roles, users, permissions and databases etc. You can’t touch schema and table(theoretically you could, but not the best practice) at this level.Now You can dopostgres=# \lto check the databases existed in your database cluster. The default output should give your three databases:
postgres template0 template1 (3 rows)
Try to connect to one of them use:
postgres=# \c postgres
Then you are entering the database level and connected with database
postgres. For some people this is very confusing because this “postgres” has the exact same name with what we installed before. But actually they are totally different things and you should know what is happening here after you check the graph I put above.So all commands you do here should be about the schema and tables. The weird things about
psql is that the boundary between schema and tables is not well presented in this tool but actually it’s a very often used and also troubled question for many users. Because after you connected to database like now, there is not further step to get into schema level just like there is no such a thing existed in Postgres. But if you want to list all tables in this level, you need to specify the schema of the table.