pg_dump and pg_restore: input file does not appear to be a valid archive
You are dumping in plain SQL format which was designed to feed to psql. This is not recognized by pg_restore. cat db.txt | psql dbname Should do the trick
postgresql
Postgresql: what does GRANT ALL PRIVILEGES ON DATABASE do?
The answers to your questions come from the online PostgreSQL 8.4 docs. GRANT ALL PRIVILEGES ON DATABASE grants the CREATE, CONNECT, and TEMPORARY privileges on a database to a role (users are properly referred to as roles). None of those privileges actually permits a role to read data from a table; SELECT privilege on the … Read more
Postgres equivalent to MySQL’s \G?
I’m not familiar enough with MySQL to know what the \G option does, but based on the documentation it looks like the psql \x option might do what you want. It’s a toggle, though, so you do it before you submit the query. \x select * from sometable;
Postgres equivalent to MySQL’s \G?
I’m not familiar enough with MySQL to know what the \G option does, but based on the documentation it looks like the psql \x option might do what you want. It’s a toggle, though, so you do it before you submit the query. \x select * from sometable;
Postgres equivalent to MySQL’s \G?
I’m not familiar enough with MySQL to know what the \G option does, but based on the documentation it looks like the psql \x option might do what you want. It’s a toggle, though, so you do it before you submit the query. \x select * from sometable;
Is there an equivalent of MySQL’s SHOW CREATE TABLE in Postgres?
You can try to trace in the PostgreSQL log file what pg_dump –table table –schema-only really does. Then you can use the same method to write your own sql function.
GRANT SELECT to all tables in postgresql
I thought it might be helpful to mention that, as of 9.0, postgres does have the syntax to grant privileges on all tables (as well as other objects) in a schema: GRANT SELECT ON ALL TABLES IN SCHEMA public TO user; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user; Here’s the link.
How to see active connections and “current activity” in PostgreSQL 8.4
OK, got it from someone else. This query should do the trick: select * from pg_stat_activity where datname=”mydatabasename”;
What’s the default superuser username/password for postgres after a new install?
CAUTION The answer about changing the UNIX password for “postgres” through “$ sudo passwd postgres” is not preferred, and can even be DANGEROUS! This is why: By default, the UNIX account “postgres” is locked, which means it cannot be logged in using a password. If you use “sudo passwd postgres”, the account is immediately unlocked. … Read more
What is a postgres superuser
https://www.postgresql.org/docs/current/static/sql-createrole.html These clauses determine whether the new role is a “superuser”, who can override all access restrictions within the database. yes – all privileges in all databases on the specified cluster