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
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
Postgres: INSERT if does not exist already
I’m using Python to write to a postgres database: But because some of my rows are identical, I get the following error: How can I write an ‘INSERT unless this row already exists’ SQL statement? I’ve seen complex statements like this recommended: But firstly, is this overkill for what I need, and secondly, how can … Read more
PG COPY error: invalid input syntax for integer
ERROR: invalid input syntax for integer: “” “” isn’t a valid integer. PostgreSQL accepts unquoted blank fields as null by default in CSV, but “” would be like writing: and fail for the same reason. If you want to deal with CSV that has things like quoted empty strings for null integers, you’ll need to feed it to PostgreSQL via … Read more
input file appears to be a text format dump. Please use psql
I take backup using and then I copy it to localhost using scp. Now when I import on my local db it gives an error by using commad line
postgres does not know where to find server configuration
Usually, when postgres is installed a service/daemon is created in the system so, there is no need to launch the server by hand. You are getting the error because the service is already running. Try to connect to the database using psql
Permission denied for relation
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: This will take care of this … Read more
eroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”
I’m developing an app on Heroku with a Postgresql backend. Periodically, I get this error message when trying to access the database, both from the CLI and from loading a page on the server: Anyone seen this before or please help point me in the right direction?
psql: FATAL: database “” does not exist
It appears that your package manager failed to create the database named $user for you. The reason that works for you is that template1 is a database created by postgres itself, and is present on all installations. You are apparently able to log in to template1, so you must have some rights assigned to you … Read more