6

Simple Backup / Restore DataBase in postgresql

view full story
linux-howto

http://androus.wordpress.com – TO make a database backup in postgreslq server you use pg_dump and psql commands. First, locate the database: # psql -l --username postgres --password Then: # pg_dump --username postgres name_db --file /dir/dir/backup.sql To restore the database: First create database with its respective name and roles: # psql postgres # postgres=# CREATE DATABASE name_db ENCODING 'UTF-8'; # postgres=# CREATE ROLE root LOGIN; Restoring: # psql name_db < [...] (HowTos)

more's picture
Submitted by more on 12/31/2009 – Made popular on 12/31/2009

Stories similar to Simple Backup / Restore DataBase in postgresql

I'm using ubuntu 12.01 and postgres 9.15.

I've installed postgres, but I can't access it for anything. Every

When I try to create a db, I get this:

Code: createdb: could not connect to database postgres: FATAL:  role "root" does not exist I googled for postgres "roles". I found syntax for creating roles.

This appears to be very messy, so I would like to remove all trace of PostgreSQL on my system. I followed various instructions found on the web, but to no avail. After that, what's the best way to install 9.1?

I need to take a Microsoft SQL Server database backup (dump) file and import/restore into a PostgreSQL database. Does anybody have some sort of transform script (maybe a perl/sed/awk script) that could be run on the MSSQL Server backup file, so that it could be restored to the PostgreSQL database? Let's assume the tables have already been created in postgres, but they are all empty.

In /var/lib/pgsql/9.1/data/pg_hba.conf, I have the following:

# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 trust

But when I execute this:

su -c "psql -d postgres" - postgres

it asks me for a password. My expectation is that users shoud not be prompted for a password.

I'm unsure what's going on here:

I've got a backup script which runs fine under root. It produces a >300kb database dump in the proper directory.

When running it as a cron job with exactly the same command however, an empty gzip file appears with nothing in it.

The cron log shows no error, just that the command has been run.

This is the script:

#!

I have a PostgreSQL 8.4 database running on Windows, but I've lost the name of the superuser username, so am unable to connect.

I've already configured pg_hba.conf to use trust to allow connections without a password, but I still need to know the name of a valid user to login.

I've tried postgres, admin etc, but I always get:

psql: FATAL: role "USERNAME" does not exist

I installed postgresql via homebrew on my 10.7.3. The install seemed successful and I was able to start up the server. However when I try to access psql I get the following error:

psql: FATAL: role "larson" does not exist

In this case larson is my username. Does anybody know what the issue is and what I can do to address it?

Postgresql Questions 18 weeks 5 days ago

I'm currently running Fedora 17 and am considering running Postgresql as a database server. In looking over the install of postgres I notice that it is installed in /var/lib and the database data is kept there also. Why is the postgres user and the database data not kept somewhere that is not so intergrated into the OS.

I want to backup a database using psql and COPY command. here is my script:

psql "user=${USERNAME} host=${HOSTNAME} dbname=${DBNAME} password=${PASSWORD}" -c \ "COPY (SELECT * FROM tbl) ORDER BY id ASC) TO STDOUT WITH CSV HEADER;" | \ bzip2 -z -f --best -c > /home/${DBNAME}-${FILENAME}.csv.bz2

Also I want to know how many lines are copied from database but psql does not have such feature.