PostgresQL Tutorial?

paperinik · 2005-06-17 07:45:46

I have searched the forum how to configure PostgresQL, but couldn't find any useful topic. It appears the majority use MysQL. Could anyone who uses PostgresQL post a step-by-step tutorial on how to configure PostgresQL after installation, including creating the db, user and etc? Also, if you're using other than Apache (lighttpd, thttpd), could you explain how the configuration will differ? Thanks.

rasat · 2005-06-17 08:19:05

I am new in postgreSQL. Managed to get started with these instructions:

Start postgreSQL:
# /etc/rc.d/postgresql start

Login username "postgres":
# su - postgres
# psql -l (view all databases)

Create an username (first login postgres):
# createuser mysamplename -P
Create a database:
# createdb mydb -O mysamplename
Remove database:
# dropdb mydp
Remove username:
#dropuser mysamplename

Dump:
# pg_dump mydb > db_str_and_data.out (single database)
# pg_dumpall > db_structure_and_data.out (all databases)
# pg_dumpall -a > db_data_only.out
# pg_dumpall -s > db_structure_only.out
Load:
# psql mydb < db_structure_and_data.out

Download phpPgAdmin to admin and maintain databases.
To login phpPgAdmin, don't use username postgres but your newly created username.
I am not sure if there is an Arch package. If not, then check here:
http://phppgadmin.sourceforge.net/

PostgreSQL Tutorial:
http://www.eskimo.com/support/PostgreSQL/tutorial/

To use PHP web interface running postgreSQL, untag this line in /etc/php.ini
;extension=pgsql.so (remove ";")

paperinik · 2005-06-17 15:10:32

rasat, thank you for taking the time to post this simple and easy to follow tutorial. I would appreciate if you can followup with some explanations. I have followed your tutorial successfully upto:

Start postgreSQL:
# /etc/rc.d/postgresql start

Login username "postgres":
# su - postgres
# psql -l (view all databases)

Create an username (first login postgres):
# createuser mysamplename -P
Create a database:
# createdb mydb -O mysamplename

Is what comes just for showing how to remove the database & username, or is it part of the tutorial that I have to follow?
Remove database:
# dropdb mydp <---(do you mean mydb?)
Remove username:
#dropuser mysamplename

Also, the following part:
Dump:
# pg_dump mydb > db_str_and_data.out (single database)
# pg_dumpall > db_structure_and_data.out (all databases)
# pg_dumpall -a > db_data_only.out
# pg_dumpall -s > db_structure_only.out
Load:
# psql mydb < db_structure_and_data.out

Do I have to choose single database, all databases or both? Thanks again.

paperinik · 2005-06-17 15:21:23

rasat, I have tried:
Dump:
# pg_dump mydb > db_str_and_data.out (single database)
# pg_dumpall > db_structure_and_data.out (all databases)
# pg_dumpall -a > db_data_only.out
# pg_dumpall -s > db_structure_only.out
Load:
# psql mydb < db_structure_and_data.out

and this is what I get:

db_str_and_data.out: Permission denied
db_structure_and_data.out: Permission denied
pg_dumpall -a > db_data_only.out: Permission denied
pg_dumpall -s > db_structure_only.out: Permission denied
psql mydb < db_structure_and_data.out: Permission denied

rasat · 2005-06-17 15:56:33

The list of postgreSQL syntax (commands) I posted is not a sequence of tutorial but samples in different situation e.g. when needed to create a new database or want to remove it.

The permission error caused when trying to dump/copy ">" to a directory where you as user "postgres" have no permission.
In root make a new directory and change the permission for the postgres. Dump the database(s) in the new directory.

paperinik · 2005-06-17 16:41:08

Thank you for the followup. Everything is now working, as my PubBB forum is successfully running with postgreSQL.

Arch Linux

#1 2005-06-17 07:45:46

PostgresQL Tutorial?

#2 2005-06-17 08:19:05

Re: PostgresQL Tutorial?

#3 2005-06-17 15:10:32

Re: PostgresQL Tutorial?

#4 2005-06-17 15:21:23

Re: PostgresQL Tutorial?

#5 2005-06-17 15:56:33

Re: PostgresQL Tutorial?

#6 2005-06-17 16:41:08

Re: PostgresQL Tutorial?

Board footer