PostgreSQL has a guide on how to best populate a database initially, and they suggest using the COPY command for bulk loading rows. The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).
pg_dump -t ‘schema-name.table-name’ –schema-only database-name More info – in the manual.
Strangely enough, the solution ended up being to change volumes: – ./postgres-data:/var/lib/postgresql to volumes: – ./postgres-data:/var/lib/postgresql/data
You may wish to read a summary of the ways to authenticate to PostgreSQL. To answer your question, there are several ways provide a password for password-based authentication: Via the password prompt. Example: psql -h uta.biocommons.org -U foo Password for user foo: In a pgpass file. See libpq-pgpass. Format: <host>:<port>:<database>:<user>:<password> With the PGPASSWORD environment variable. … Read more
In psql that would be \dx See the manual of psql for details. Doing it in plain SQL it would be a select on pg_extension: SELECT * FROM pg_extension;
9.5 and newer: PostgreSQL 9.5 and newer support INSERT … ON CONFLICT (key) DO UPDATE (and ON CONFLICT (key) DO NOTHING), i.e. upsert. Comparison with ON DUPLICATE KEY UPDATE. Quick explanation. For usage see the manual – specifically the conflict_action clause in the syntax diagram, and the explanatory text. Unlike the solutions for 9.4 and … Read more
Extract the table and pipe it directly to the target database: pg_dump -t table_to_copy source_db | psql target_db Note: If the other database already has the table set up, you should use the -a flag to import data only, else you may see weird errors like “Out of memory”: pg_dump -a -t table_to_copy source_db | … Read more
From psql’s help (\?): \o [FILE] send all query results to file or |pipe The sequence of commands will look like this: [wist@scifres ~]$ psql db Welcome to psql 8.3.6, the PostgreSQL interactive terminal db=>\o out.txt db=>\dt Then any db operation output will be written to out.txt. Enter ‘\o’ to revert the output back to … Read more
( tl;dr : goto option 3: INSERT with RETURNING ) Recall that in postgresql there is no “id” concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type). If you are interested in getting the id of a newly inserted row, there … Read more
GRANT on the database is not what you need. Grant on the tables directly. Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions. You want instead: GRANT ALL PRIVILEGES ON TABLE side_adzone … Read more