Category: PostgreSQL

  • PostgreSQL connect to database using psql

    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

  • 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

  • PostgreSQL Export table into CSV file

    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.

  • PostgreSQL List all databases

    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

  • 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
  • PostgreSQL Delete a Database

    PostgreSQL Delete a Database

    To delete a PostgreSQL database, run

    DROP DATABASE DB_NAME;

    Example

    DROP DATABASE "odoodb2";

    PostgreSQL drop database

  • 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