As I am just starting out with Postgres, please leave me a comment if you see something that is incorrect or inaccurate!
I got these instructions on how to install Postgres on linux from Chris Oliver’s blog:
sudo apt-get -y install postgresql libpq-dev
Postgres will create a user account on your computer called ‘postgres’. To log in for the first time to do the initial setup:
sudo su postgres # switch to the new user on your computer called 'postgres' psql # connect to the postgres server
Once logged in as the postgres superuser, create a new user who is NOT a super user, but still has power to create databases and create other users. As far as I can tell, “user” and “role” mean exactly the same thing in late versions of Postgres:
create role [rolename] CREATEDB CREATEROLE with PASSWORD 'myPassword';
Things are easier if you name your:
- Linux computer username
- Postgres username, and
- Postgres database name
are all exactly the same
For instance, if they are all named “teddy”, then from your computer’s “teddy” user, if you type
in the command line without specifying a username, password, or database, Postgres will automatically connect you as psql user “teddy” to the database named “teddy”, and won’t prompt you for a password.
That’s why when you did
sudo su postgres && psql earlier, you were connected as the psql user postgres without being prompted for a password. Of course, you can still specify a custom username, password, and database to connect to. But it is just much easier if you make everything match up from the start.
To configure Postgres so you can connect remotely to it, see this Stack Overflow entry.
To start, stop, or restart Postgres
sudo /etc/init.d/postgresql start sudo /etc/init.d/postgresql stop sudo /etc/init.d/postgresql restart
Useful commands once you are in the psql program:
\q # quit \l # list all the databases \d db # list the tables, etc., in the database named 'db' \du # list all the users/roles \connect db # connect to the database named 'db' \? # the help menu!
As far as I can tell, unless you are a superuser (and it is NOT recommended to regularly use Postgres as a superuser), you can only connect to one specific database at a time. You can’t just connect to “the whole server”. So you have to create at least one database BEFORE you can connect to psql. You can use this utility to create a db from outside the psql program:
# create db called mydb, username is myuser, the host IP is 18.104.22.168, -e echos the drop db command sent to server so you can see what is going on createdb -U myuser -h 22.214.171.124 -e mydb
It is best that the postgres user who will be using the database be the one to create it, instead of the postgres super user being the one to create it. That way, the user has all the privileges they need on that database. But if a different user created the database than will be using it, then you can still grant privileges on the database to whomever needs them:
sudo su postgres psql GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
Since you cannot drop a database when you are connected to it, you can drop it from outside the psql program using this utility:
# drop database mydb, user is myuser, host is 126.96.36.199, -i for double-confirm, -e is an echo so you can see what command was sent to the server. dropdb -U myuser -h 188.8.131.52 -i -e mydb
Importing and exporting CSV:
copy TableName from '/data/my_csv_file.csv' using delimiters ';' \copy TableName to '~/data/my_csv_file.csv' delimiter ';' csv header;