PostgreSQL notes

From myWiki
Revision as of 11:45, 11 January 2022 by Stevet (talk | contribs) (Created page with "Linux - change to postgres user in command line interface # sudo su - postgres Backup database # pg_dump db_name > file_name Restore pg_dump file # psql db_name < f...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Linux - change to postgres user in command line interface

  # sudo su - postgres

Backup database

  # pg_dump db_name > file_name

Restore pg_dump file

  # psql db_name < file_name

Some table modification commands

  ALTER TABLE some_table_name RENAME "row.names" TO id;
  ALTER TABLE some_table_name ALTER COLUMN id TYPE integer USING id::integer;
  ALTER TABLE some_table_name ADD PRIMARY KEY (id);

list databases

  postgres # psql
  postgres=# \l

dump from one host to another

  pg_dump -h host1 dbname | psql -h host2 dbname

Reload with

  psql dbname < archive.sql

Create a database

  createdb dbname

Using a zip archive with backup and restore

  pg_dump dbname | gzip > filename.gz
  gunzip -c filename.gz | psql dbname

remote backup

  pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sql

Changing user privileges

  ALTER USER user_name WITH SUPERUSER/NOSUPERUSER;