Category: PostgreSQL

PostgreSQL connect to database using psql
To connect to the PostgreSQL database server using psql command line, run
```
su - postgres
psql
```
If you have a PostgreSQL user and password, you can use
```
psql -h SERVER_IP_HERE -p 5432 -U USER_NAME_HERE -W
```
Example
```
psql -h students.arrhwfpckmeo.ap-southeast-2.rds.amazonaws.com -p 5432 -U aite_students -W
```
-W option is to force asking password.

See PostgreSQL
March 1, 2021
postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files
When i restart postgresql, i get following error
```
postgres@vmi465483:~$ systemctl restart postgresql.service
Failed to restart postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files
See system logs and 'systemctl status postgresql.service' for details.
postgres@vmi465483:~$ 
```
This is because you are running the command as user postres.

Login as user “root”, you will be able to restart postgres with command
```
systemctl restart postgresql
```
See PostgreSQL
January 31, 2021
PostgreSQL Export table into CSV file
To export a table into CSV file, run
```
\copy TABLE_NAME to 'FILE_NAME.csv' csv header;
```
This will save the CSV file in current directory.
June 5, 2019
PostgreSQL List all databases
To list all databases in the PostgreSQL database, first connect to the PostgreSQL database using psql command
```
su - postgres
psql
```
Now run
```
\list
```
This will list all available databases.

To change to a database, use the command /connect
```
\connect DBNAME
```
Once you are connected to a database, you can list all tables in a database with the command
```
\dt
```
February 7, 2019

PostgreSQL Create Database

To create a database, run

CREATE DATABASE "DB_NAME_HERE";

Creating user, grant permissions

CREATE USER USER_NAME_HERE WITH PASSWORD 'BAPT788HZutBdV';

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE;
GRANT USAGE ON SCHEMA public TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON DATABASE DB_NAME_HERE TO USER_NAME_HERE;

psql DB_NAME_HERE -c "GRANT ALL ON ALL TABLES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to USER_NAME_HERE;"

Creating a User and Database using psql

su - postgres
createuser USERNAME_HERE
createdb --encoding=UTF8 --owner=USERNAME_HERE DB_NAME_HERE

November 17, 2018

PostgreSQL Delete a Database
To delete a PostgreSQL database, run
```
DROP DATABASE DB_NAME;
```
Example
```
DROP DATABASE "odoodb2";
```
November 17, 2018
pg_ctl
pg_ctl is used to start and stop the PostgreSQL server.

To start PostgreSQL, run
```
pg_ctl start -D /path/to/datadir
```
You can also use stop/restart.
```
pg_ctl stop -D /path/to/datadir
pg_ctl restart -D /path/to/datadir
```
Back to PostgreSQL
March 10, 2018